vba

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

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

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

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

You will need to fill in the address into the string. .Formula = “=COUNTIF(” & ReqItems.Address & “,””>0″”)” Please also note that Dim ReqItems, SupItems As Range only declares SupItems As Range but ReqItems will be of type Variant here. You will need to declare a type for every variable. Dim ReqItems As Range, SupItems … Read more

Handling the infamous VBA Errors (2042) successfully!? Before using this code be sure you have studied at least the customize section carefully or you might lose data. Most importantly the second column must always be adjacent to the right of the first column, otherwise this code couldn’t have been done with the ‘array copy-paste version’. … Read more