Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Do loop question

Posted on 2013-12-30
Medium Priority
252 Views
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"
0
Question by:Justincut
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 15

Expert Comment

ID: 39746234
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
``````
0

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 39746281
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"
``````

for example.
0

Author Comment

ID: 39746403
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

Author Comment

ID: 39746537
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

LVL 85

Expert Comment

ID: 39746741
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

Author Comment

ID: 39746901
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month8 days, 6 hours left to enroll