[Solved] VBA to Find and Remove [closed]

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

[Solved] VBA code or macro copying cell value [closed]

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

[Solved] VBA Excel Optimization for ForLoop [closed]

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

[Solved] Extract One Column in a Table from a CSV Hyperlink to Excel Using VBA [closed]

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

[Solved] Excel VBA assign hyperlink to a cell

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

[Solved] Excel macro to find cells with numbers larger than 20 in a specific column and then divide the matching cells by 1000 [closed]

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

[Solved] Return list of integer values next string values

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

[Solved] Excel VBA – Referencing a named range in a formula

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

[Solved] Calculating new cells containing True/False outputs from cells also containing #N/A values using VBA

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