I need to lookup values on another sheet. My formula works great, when i use it manually =VLOOKUP(TRIM(C2),TRIM(‘MyDataSheet’!$A$1:$E$500),4,FALSE) However, I need to be able to plug this formula into a cell dynamically using VBA. This is what I tried: Set lookupRange = ThisWorkbook.Sheets("MyDataSheet").Range("A1:E500") Set newCol = tbl.ListColumns.Add newCol.DataBodyRange.FormulaR1C1 = "=VLOOKUP(TRIM(RC[-16])," & lookupRange.Address(True, True, xlR1C1) &… Read More How to include dynamic VLOOKUP in FormulaR1C1 VBA?

I’d like to copy like this. But, this is what I got My code is quite simple Range("C1:C2, D1:D2").Value = Range("A1:A2").Value I just want to copy several cells as a whole into several different places at once using Range propery like this. Does it has something relationship with Order of Operation ? I mean between… Read More How to use Range property properly?

I’ve written my code to run on the second row and now I want to let it run on the first 20 rows if I indicate column 23 with an "x". I can’t seem to make it work somehow: Dim i as Long Dim j as long Dim Seat as String i = 2 For… Read More Run code on first 20 rows if column is indicated as X

I am trying to sort a VBA dictionary with the following keys and values. It is sorting the keys alphabetically. What I want is for it to treat the keys as "MMM-DD" format and sort. Is it possible? How do I make array list convert the keys to a date format (MMM-DD)? May-1 12 Jan-21… Read More Sort VB Dictionary with Keys in "MMM-DD" format

Hi I use a code to copy paste from a one drive excel sheet to another This is my code to open the file: Public openwb As Workbook Sub testOpenWBOneDrive() Dim wbFullName, objLogExcel As Object Set objLogExcel = CreateObject("Excel.Application") objLogExcel.Visible = True wbFullName = "https://mysmartplace-my.sharepoint.com/personal/Misc/data.xlsx" Set openwb = objLogExcel.Workbooks.Open(wbFullName) End Sub And my code to… Read More Excel to close trailing window

Here is the code: Private Sub Query1_Change(ByVal Target As Range) Dim myRange As Range Dim myCell As Range Dim wsComments As Worksheet Dim wsQuery1 As Worksheet Dim lastRow As Long Dim i As Long Dim foundMatch As Boolean Set wsComments = ThisWorkbook.Sheets("Comments") Set wsQuery1 = ThisWorkbook.Sheets("Query1") If Target.Row < 2 Or (Target.Column <> 31 Or… Read More Why won't this VBA code write or update records to a new tab?

I have written this piece of code: thisworkbook.Sheets("test_destination").Range("J2:K" & lastrow_orgid).Value = Application.WorksheetFunction.Unique(diesesworkbook.Sheets("test_source").Range("J2:K" & lastrow_orgid).Value) It does what it is supposed to do but it creates these #NV values. Maybe someone could tell me if there is another way to write this to avoid these #NV values. >Solution : Couple of things. Work with objects. This… Read More avoid #NV values with excel vba

i have a code below it was working fine then suddenly the line arr = split(cell.Value, ",") has an error wrong number of arguments or invalid property assignment i just inserted the lastRow variable, even if i changed the range something fixed like A1:A10 it is still the same error Sub split() Dim rng As… Read More split then add to array working before then suddenly not working

I had a code that would place 1 or 2 values in a cell at random (The values were either "SF" or "BM") and I used to do it this way Worksheets("Sheet1").Range("A1").Value = IIf(Rnd() > 0.5, "BM", "JC") The problem is that I want to add a new third value "KS" and doing the following… Read More Place one of three values in a cell at radom

I’m new to Excel VBA and I’m having trouble understanding how to create a simple line chart, with multiple lines. I have done multiple tutorials and everytime I want to customize my code, I look online and find a totally different syntax for creating a chart. What is the best method to configure a chart.… Read More How to configure a Chart