Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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"
0
Justincut
Asked:
Justincut
  • 3
  • 2
1 Solution
 
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
 
Rory ArchibaldCommented:
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now