0

I am trying to make a table on one sheet that pulls cell values from other sheets depending on a indictor value in cell "C15". I am thinking I can continue doing "if" statements for each of the twelve indicators but first I need to correct what I have for the first indicator value. I have tried a few things but using the "...Formula="=vlookup..." seems to have been the easiest.

The issue comes with looping inside the formulas. It should start by looking up the value of "L6" on a certain sheet determined by the indicator. In this case, it's doing vlookup for three values from "H20_brine density", copying them to the active sheet, doing some math to get value "M6", and then it should repeat using "L7" (which is equal to "M6") until the rows have been completed. The "rows" are determined by another indicator and works fine.

Range("L6").Value = Range("C12")

If Range("C15").Value = 1 Then
    For i = 6 To rows + 6
    
        With autostatic
            .Range("N" & i).Formula = "=vlookup(Li,'H2O_brine density tables'!AA$5:AG$80,5,TRUE)"
            .Range("O" & i).Formula = "=vlookup(Li,'H2O_brine density tables'!AA$5:AG$80,6,TRUE)"
            .Range("P" & i).Formula = "=vlookup(Li,'H2O_brine density tables'!AA$5:AG$80,7,TRUE)"
            .Range("k" & i).Formula = "=Ji*Ni^2+Ji*Oi+Pi"
            .Range("M" & i).Formula = "=Li+Ki*C13"
            .Range("L" & i + 1).Formula = "=Mi"
        
        
        End With
    Next
End If
Cole Snody
  • 5
  • 4
  • You don't need a loop to do this. You can write a formula to a multi-cell range in one step. Excel will automatically update relative references as you go down rows or across columns. – BigBen Sep 02 '22 at 17:09
  • In other words: `Range("N6:N" & rows + 6).Formula = "=vlookup(L6,'H2O_brine density tables'!AA$5:AG$80,5,TRUE)"` and so on. – BigBen Sep 02 '22 at 17:11
  • BigBen, that seems good but it should not look up L6 each time, it need to lookup L7 on the second iteration and so forth – Cole Snody Sep 02 '22 at 17:16
  • It won't look up `L6` every time. Excel will update the `6` to `7`, `8`, `9` etc. as you go down the column. `L6` is a *relative* reference, which as I mentioned will be updated by Excel. Try it and your mind may be blown. – BigBen Sep 02 '22 at 17:17
  • 1
    Works perfect, who was I to question an expert? Thanks BigBen – Cole Snody Sep 02 '22 at 17:25

0 Answers0