So the problem turned out to be that because this code was run from word, with the Excel library as a reference, the Range type defaulted to a MS-Word Range object instead of a MS-Excel Range object, meaning that my function was looking for the wrong type of parameter. Here is the corrected code (notice … Read more
With your latest explanation and comparing your example results with mine, I believe I now understand your requirement. You have the parameters placed on a worksheet and output the results to the same worksheet. I do not want to tie the generation code to a particular style of input or output and have coded a … Read more
@David Zemens idea.. this should work .Tested Sub test() Dim str As String Dim s() As String str = “15f,158,1669,187,15,156,47t” s = Split(str, “,”) For i = LBound(s) To UBound(s) If Len(s(i)) = 3 Then t = IIf(t = “”, s(i), t & “,” & s(i)) End If Next i MsgBox (t) End Sub solved … Read more
Remove Column Titles From Column Data It is assumed that the data (table) starts in cell A1 in worksheet Sheet1 of the workbook containing this code (ThisWorkbook). This is mostly useful for a one-time operation because it will only copy values. It will not copy formulas and formats. Adjust the worksheet (tab) names. Option Explicit … Read more
try this: Sub AddName() Dim lr As Long, i As Long, count As Long Dim Team As String, Sport As String, NewPlayer As String Team = “Pink Team” Sport = InputBox(“What Sport do you wish to add a name to?”, “Sport”) NewPlayer = InputBox(“What is the name of the player you wish to add?”, “New … Read more
Create a source array for your fruits. Dim fruits fruits = Array(“Apple”, “Orange”, … , “Mango”) Then use a Loop to assign values to Range. You’ll need additional variables for it. Dim n As Long, fruit With Selection: n = 0 For Each fruit In fruits .Offset(n) = fruit: n = n + 1 Next … Read more
Try this =IF(A1<25,”Young”,IF(AND(A1>=25,A1<=65),”Middle Aged”,”Old”)) 0 solved How to define a range of values while writing IF condition in Excel?
Do you have a list of the names of the files you want to open? If you have it you can loop trough it setting a workbook as the name of each file. Es. Dim x as integer Dim selection, nameofthefile as variant Dim wk1 As Workbook Dim sh1 As Worksheet Dim wk2 As Workbook … Read more
Sub dataImport() Dim wbImport As Workbook Dim wksImport As Worksheet Dim rngFind As Range ‘/ Open the CSV Set wbImport = Workbooks.Open(“https://www.cboe.org/publish/restrictionsall/cboerestrictedseries.csv”) Set wksImport = wbImport.Worksheets(1) ‘/ Remove date stamp wksImport.Rows(1).EntireRow.Delete ‘/ Search for OPT_CLASS header Set rngFind = wksImport.UsedRange.Cells.Find(“OPT_CLASS”) If Not rngFind Is Nothing Then ‘/ Found it ‘/ Copy and paste to column … Read more
You can try using below code, it will loop through all lines and compare column A with column G:G if its equal then compare C & H and if is not equal then change the colour index. Code: Dim Wb As Workbook, ws As Worksheet, lrow As Long, j As Long, m As Long, lrow2 … Read more
Here’s a sample of how to do that: Sub createLink() Dim lastRow As Integer, sheetCount As Integer, myRange As Excel.Range, c As Excel.Range lastRow = Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row sheetCount = Application.Sheets.Count Set myRange = Excel.ThisWorkbook.Sheets(“Sheet1”).Range(“B1:B” & lastRow) For Each c In myRange For x = 1 To sheetCount If Worksheets(x).Name = c.Value Then Excel.ThisWorkbook.Sheets(“Sheet1”).Hyperlinks.Add Anchor:=c, … Read more
Doable with formulas and and helper column: In column E (Helper): =IF(A2=A1,””,A2) In Column C (Output): =IF(E2=””,C1,CHAR(COUNTIF($E$2:E2,E2)+64)) 0 solved Repeating series of numbers-unique id needed
Let’s say your Kb values start in row 1 of column A, write this formula in column B : =IF(A1>20, A1/1000, A1) Basically, what this does, is tell the computer that if A1 is greater than 20, put A1/1000 in this cell, otherwise put A1. Stretching this formula down the column will give you the … Read more
As per comments I’ve amended the code to copy only the specified range, both Sheets should exist, the code will not create the second Sheet for you: Sub Test() Dim Cell As Range With Sheets(“Sheet1”) ‘Sheet with data to check for value ‘ loop column H untill last cell with value (not entire column) For … Read more
This works for me: Public Function GETMATCHES(ByVal match As String, ByVal cells As Range, ByVal columnOffset As Integer) As String Dim result As String Dim cell As Range For Each cell In cells If cell.Value2 = match Then result = result & “,” & cell.Offset(0, columnOffset).Value2 End If Next If Len(result) > 0 Then result … Read more