Variable Vlookup with Do-While loop <<URGENT! :(>>

Looking for some literature since morning. Please help me out on this one

Main_Var_vlookup.xaml (10.3 KB)

Heyy @KP6689

Can you explain the issue you are getting so that we can help you out with the issue?

Just to give you some info on VLookup’s, you need to use the variable of type string and assign the formula to the string and then use write cell activity to get the required value to a particular cell and then auto fill range activity to get all the values

the formula, isnt getting applied on all the rows in that column.

Plus the formula isnt taking the variable as if it is hard-coded

image

below is the formula for vlookup
“=IF(VLOOKUP(a,[fff.xlsx]SQLExport!$B$2:$B$32075,1,0)=a,1,0)”

and for range increment
“C”+(Val +1).ToString

1 Like

=IF(VLOOKUP(a,[fff.xlsx]SQLExport!$B$2:$B$32075,1,0)=a,1,0)

Here “a” is the variable right?

If so, then you need to pass it like

“=IF(VLOOKUP(”+a+",[fff.xlsx]SQLExport!$B$2:$B$32075,1,0)="+a+",1,0)"

, if there are any other dynamic values you are passing in the formula,. then you need to pass it as the variables not as the string…

let me check it asap

also, please check since a = A + Val(which is incrementing), I had a hard time figuring out the variable type for it. Any help in this regard.

After changing the vloolup formula as you mentioned, it throws that error only

1 Like

In the variables pane below, you need to change the type of variable to string as we are appending the string to the existing or we are concatenating

I tried that too (maybe Im wrong, please correct).

The idea that i have is to keep both A & Val as same type - such that they can clubbed together. But the initial value of Val which is 0 is preventing it from happening.

And whenever they arent equal this happens

1 Like

Also for a , which is = “A”+Val, assigning it Generic Value isnt helping either

As you are using Var variable to increment the count, it should be of type int32 so that you can increment it everytime and “a” variable should be of type string, so
in the assign activity, you need to give as

a = “A” + val.ToString

which you are converting the number to string.

checking it…

Good sir, that one problem is solved, and now Im checking the aspect of rolling vlookup! notify you asap

1 Like

please dont close the thread

You need to use Auto Fill Range activity to get the data for all the rows in excel.

This one is in your hand :smiley: as you have created the topic.

output stays one cell behind on it

image

i will just check it

You need to write the value from second cell here, so you need to pass the value as A2,
but, you are assigning the value as 0 and incrementing it with 1, so it will be A1. So, assign the value with 1 which will increase by 1 and write from second cell

nopes, please check the starting value is 1 here, has it got something to do with the maximum possible value of Val
as defined by Val = Check1.Rows.Count

i also thought + 1 to it, didnt work

You need to get the value for the row A2 and write it in some column second row, till the end of the rows data, so better to use auto fill range.

Can you let me know how you are using this? this will help you in looping through the number of times you want to loop, else if you are using auto fill range, then it will be useful.

please note that whenever i try to add one in Do-while loop’s condition, it just doesnt work…do help