VBA trigger enters or updates search values

Question

I have the following code:

Sub PrintToCSV() Dim i As Long, e As Long i = Worksheets("STATEMENT (2)").Range("$G$6").Value e = Worksheets("STATEMENT (2)").Range("$G$7").Value Do While i <= e Range("K6") = i Application.Wait (Now + #12:00:01 AM#) If Range("$X$10").Value > 0 Then Cells(1, 1).Value = i End If i = i + 1 Loop End Sub 

It cyclically changes the Range("K6") value Range("K6") as expected. However, the Range("K6") value Range("K6") updates the other cell values ​​(vlookup) when I do it manually, but not with this code. How can I guarantee that the values ​​of other cells are affected by Range("K6") changes using this code?

Answer 1

The problem lies in the type mismatch. Range("K6") value is a long integer, and the lookup table stores account numbers as text. Converting text to number solves the problem.

Answer 2

Just FYI - don't declare it like this:

 Dim i,e as long 

because for this declaration, only "e" is declared long, and "i" is declared an option. This can cause problems somewhere later.

The right way:

 Dim i as long Dim e as long 

Answer 3

Here you got an error in your code because i was undefined. And that should be fixed with Option Explicit - if we were in pure vb .

This is a common declaration problem in which we accept vba will read

 Dim i,e as long 

as

 Dim i as long Dim e as long ... 

Unfortunately, this is not the case. This is weird because it is different from how it works in vb

Declaring multiple variables

You can declare several variables in one declaration by specifying a variable name for each of them and after each array name with parentheses. Several variables are separated by commas.

 Dim lastTime, nextTime, allTimes() As Date 

In VBA, to verify the type , we can check the type of the variable in this way using TypeName :

 Sub getTypes() Dim i, e As Long MsgBox "i: " & TypeName(i) MsgBox "e: " & TypeName(e) End Sub 

give:

 i: Empty e: Long