For Loop Question 1

[wayneryeven]

Hey all
I have written the following code:

Range("k2").Select

ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],ActualProduction,2,FALSE)),0,(VLOOKUP(RC[-2],ActualProduction,2,FALSE)))"
ActiveCell.Value = ActiveCell.Value

' go to next row...
ActiveCell.Offset(1, 0).Select
Next i

Essentially what this does is loop through each row and perform a lookup, then place the VALUE of this lookup in the cell (Thanks PHV for help on this).

Now, there MAY already be a value in the cell to begin with, so what i wish to do is the following:

Read the value in the cell and copy to a variable aBalance
Perform the lookup and either a) copy the value to the cell or b) write the lookup result to the variable aProd
Then sum the two variables aBalance+aProd and write the answer to the Active Cell.


I have the lookup working. However i cant seem to get it to read the value in first for each loop.

Has anyone any ideas or pointers?

All help greatly appreciated.

 

[PHV]

Something like this ?
Range("k2").Select
aBalance = Val(ActiveCell.Value)
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],ActualProduction,2,FALSE)),0,(VLOOKUP(RC[-2],ActualProduction,2,FALSE)))"
aProd = ActiveCell.Value
ActiveCell.Value = aBalance + aProd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wayneryeven]

hey PHV
its working....to a point!

Whats happening is that it looks like its taking the beginning cells aBalance and adding aProd to this value each time, as opposed to reading each rows aBalance?

Thus
A: aBalance= 40
aProd= 40
ActiveCell.Value = aBalance + aProd = 80

B: aBalance= 80
aProd= 20
ActiveCell.Value = aBalance + aProd = 60
(Here it is taking aBalance of 40 and adding the 20 to get 60)

This happens then in subsequent rows where the aBalance=40 is being used, as opposed to what the value is actually for each row.

I tried setting aBalance to 0 before aBalance = Val(ActiveCell.Value) to no avail.

Anyone any ideas?

Thanks v much in advance, i will continue to have a look at it and see if i can get to the root cause.

 

[PHV]

Sorry, I quite don't understand what you're trying to do.

 

[ck1999]

Please repost your code and show where you your loop is. Do you have the aBalance = Val(ActiveCell.Value) outside of the loop?

ck1999

 

[wayneryeven]

PHV-
i have 3 rows of data

Batch No aBalance aProd
Row 1- 1 40 50
Row 2- 2 40 0
Row 3- 3 40 60

aProd is a lookup (done as in code above).

What i want to do is to LOOP through each row, doing a lookup against Batch number to find aProd. I also want to write the aBalance to a variable. When i find aProd i want to take aBalance and add it to aProd and write the new sum to aBalance.

Hence for the table above it would now look like this after the loop:

Batch No aBalance aProd
Row 1- 1 90
Row 2- 2 40
Row 3- 3 100

I hope this clarifies what i am trying to achieve?

 

[PHV]

Something like this ?
Range("k2").Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],ActualProduction,2,FALSE)),0,(VLOOKUP(RC[-2],ActualProduction,2,FALSE)))"
ActiveCell.Offset(0, -1) = ActiveCell.Offset(0, -1) + ActiveCell.Value
ActiveCell.Value = 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wayneryeven]

cool PHV- that worked a trest!

Im wondering is it possible to do away with column aProd completely? (as this gets its value from lookup). Would it be possible to assign this value to a variable and add to aBalance OR is this is something that would be difficult to achieve since we use Active Cell to loop through each row?