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 loops and changes value of Range("K6") as expected. However, the value of Range("K6") updates other cells values (vlookup) when I do it manually, but not with this code. How can I ensure the values of other cells depended on Range("K6") changes with this code?
3 Answers
Answers 1
Just FYI - do not declare like this:
Dim i,e as long because for this declaration only "e" is declared as long and "i" as a variant. This may cause problems somewhere later.
The correct way is:
Dim i as long Dim e as long Answers 2
The problem lays in the type mismatch. Range("K6") value is a long integer, while lookup table stored account numbers as text. Converting text to a number solved the problem.
Answers 3
Here you got a bug in your code because i type was undefined. And this should be fixed with Option Explicit - if we were in pure vb.
This is a common declaration issue where we assume vba will read
Dim i,e as long as
Dim i as long Dim e as long ... Unfortunately it doesn't. It is weird, because it differs from the way it works in vb:
Declaring Multiple Variables
You can declare several variables in one declaration statement, specifying the variable name for each one, and following each array name with parentheses. Multiple variables are separated by commas.
Dim lastTime, nextTime, allTimes() As Date
In VBA, to be sure of the type, we can get check the type of the variable that way with TypeName:
Sub getTypes() Dim i, e As Long MsgBox "i: " & TypeName(i) MsgBox "e: " & TypeName(e) End Sub give:
i: Empty e: Long 
0 comments:
Post a Comment