Do loop question

Hi Guys, if I have a Else if statement in a Do Loop and it satisfies the criteria: eg see below
and I want to put a Vlookup Formula in. How do I change the Range C4603 Vlookup  as a variable ?
Dim i As Integer
Worksheets("Rec").Select

i = 5


While Cells(i, 5).Value <> ""

ElseIf Cells(i, 18).Value = "4438272" Then

Cells(i, 27).Formula = "=IF(ISNA(VLOOKUP(C4603,'Bond pricing'!B:M,12,FALSE)),0,VLOOKUP(C4603,'Bond pricing'!B:M,12,FALSE))*0"
JustincutAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Is the C4603 part supposed to relate to the i counter in some way? If so, you can use:
Cells(i, 27).Formula = "=IF(ISNA(VLOOKUP(C" & i & ",'Bond pricing'!B:M,12,FALSE)),0,VLOOKUP(C" & i & ",'Bond pricing'!B:M,12,FALSE))*0"

Open in new window


for example.
0
 
Simon BallCommented:
its just an IF statement in the Do ..Loop.

for the 2nd part of the question you'll need to declare a range and set it = to the range you wish, and pass that if your working with the range in VBA...

as you're outputting a string with a range in, you can just declare a string, set it = to the value you require, and concatentate it into the "=if" string

e.g.
dim myVlookup as string
myVlookup = "VLOOKUP(C4603,'Bond pricing'!B:M,12,FALSE)"

dim myCellFormula as string
'CellFormula = "=IF(ISNA(VLOOKUP(C4603,'Bond pricing'!B:M,12,FALSE)),0,VLOOKUP(C4603,'Bond pricing'!B:M,12,FALSE))*0"

myCellFormula = "=IF(ISNA(" & myVlookup  & "),0," & myVlookup  & "))*0"

Cells(i, 27).Formula =myCellFormula 

Open in new window

0
 
JustincutAuthor Commented:
Can't you use offset eg.

While Cells(i, 5).Value <> ""

ElseIf Cells(i, 18).Value = "4438272" Then

Cells(i, 27).Formula = "=IF(ISNA(VLOOKUP(Offset[-24]),'Bond pricing'!B:M,12,FALSE))?

The Vlookup is looking at a range 24 columns to its left thus Offset [-24]
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
JustincutAuthor Commented:
The C4603 part has nothing to do with it. Its Looping down Column 27 and the Vlookup is looking at Column C. The row depends on whether the If statement is satisfied or not,
0
 
Rory ArchibaldCommented:
That comment makes no sense to me whatsoever, I'm afraid. If the C4603 part has nothing to do with it, why have you replaced it with Offset(-24) in your formula?
0
 
JustincutAuthor Commented:
Hi, on my Rec, row 4603 triggered the Macro as the IF statement was satisfied. It put a Vlookup in column 27 which was looking up a range in column 3. Thus AD4603 would have looked up a Range in C4603, which is 24 columns to left.I have to do this Rec everyday and its on a different row everyday, that's why it has to be a variable.

IF(ISNA(VLOOKUP(Offset[-24]),'Bond pricing'!B:M,12,FALSE))?,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.