| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect & Named Range
Hi
I have been going around and around with this. I have finally isolated my problem as being the insertion formula. I suspect my problem is the formatting of either the Indirect, VLookup or the named range. Is there something special I need to do to insert these via code? Everything I've tried so far has not worked. Thanks My formula: =IF(ISBLANK($J36),"",IF($J36="No",0,INDIRECT(VLOOK UP('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect & Named Range
Hi,
Sorry, my insertion formula is My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" "Karen53" wrote: Hi I have been going around and around with this. I have finally isolated my problem as being the insertion formula. I suspect my problem is the formatting of either the Indirect, VLookup or the named range. Is there something special I need to do to insert these via code? Everything I've tried so far has not worked. Thanks My formula: =IF(ISBLANK($J36),"",IF($J36="No",0,INDIRECT(VLOOK UP('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect & Named Range
"=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _
"0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" and iCtr must be greater than 21 otherwise you get that problem of an invalid cell again. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Hi, Sorry, my insertion formula is My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" "Karen53" wrote: Hi I have been going around and around with this. I have finally isolated my problem as being the insertion formula. I suspect my problem is the formatting of either the Indirect, VLookup or the named range. Is there something special I need to do to insert these via code? Everything I've tried so far has not worked. Thanks My formula: =IF(ISBLANK($J36),"",IF($J36="No",0,INDIRECT(VLOOK UP('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect & Named Range
Thank you, Bob. I got it. I thought I was going to go nuts with this.
-- Karen "Bob Phillips" wrote: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" and iCtr must be greater than 21 otherwise you get that problem of an invalid cell again. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Hi, Sorry, my insertion formula is My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" "Karen53" wrote: Hi I have been going around and around with this. I have finally isolated my problem as being the insertion formula. I suspect my problem is the formatting of either the Indirect, VLookup or the named range. Is there something special I need to do to insert these via code? Everything I've tried so far has not worked. Thanks My formula: =IF(ISBLANK($J36),"",IF($J36="No",0,INDIRECT(VLOOK UP('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect & Named Range
Bob,
I feel like I am going nuts. This was working this morning. I saved it and worked in another part of the workbook which at this point is unrelated to this sheet. I go back to this sheet and this no longer works. I don't get what is happening. It is impossilble for ictr to be less than 21 as it starts at 36. I need to understand why this happened. I've gone through this again trying to find the problem. I've messed with it now and probalby made it worse but I can't see what is or was wrong. I'm getting pretty frustrated. Why would it work and then not work when I hadn't made any changes to this code? Here is the complete routine. Thanks, Karen Option Explicit ' ' 'Master CAM worksheet procedures Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B26" Dim iCtr As Long Dim sNo As String Dim sYes As String Dim sPercentYes As String Dim sPercentNo As String Dim LastRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then LastRow = 337 If Range("B26").Value = "Yes" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share formula sYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & _ "=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _ "C16,IF(ISBLANK(R3C2),(R" & iCtr & "C11* R" & iCtr & _ "C9),(R" & iCtr & "C11* R" & iCtr & "C9)/365*(R3C2)))))" Me.Range("L" & iCtr).FormulaR1C1 = sYes 'original formula =IF(ISBLANK($J36),"",IF($J36="No",0,IF(ISNUMBER($P 36),$P36,IF(ISBLANK($B$3),($K36*$I36),($K36*$I36)/365*($B$3))))) Next ElseIf Range("B26").Value = "No" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share formula sNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _ "IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _ "C16,IF(ISBLANK(R3C2),(R" & iCtr & "C11* R" & iCtr & _ "C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R3C2))))" Me.Range("L" & iCtr).FormulaR1C1 = sNo 'original formula =IF(ISBLANK($J36),"",IF(ISNUMBER($P36),$P36,IF(ISB LANK($B$3),($K36*$G36),($K36*$G36)/365*($B$3)))) Next End If End If If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then LastRow = 337 If Range("B26").Value = "Yes" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share Percentage - line items begin on row 15, so 36-15 = 21 sPercentYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" Me.Range("K" & iCtr).FormulaR1C1 = sPercentYes 'original formula =IF(ISBLANK(J36),"",IF(J36="No",0,INDIRECT(VLOOKUP ('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) Next ElseIf Range("B26").Value = "No" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share Percentage - line items begin on row 15, so 36-15 = 21 sPercentNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _ "Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" Me.Range("K" & iCtr).FormulaR1C1 = sPercentNo 'original formula =IF(ISBLANK(J36),"",INDIRECT(VLOOKUP('Line Items'!$C15,CAMPerCentLoc,3,FALSE))) Next End If End If If Not Intersect(Target, Me.Range("B28:B30")) Is Nothing Then 'Set the CAP label If Range("B28").Value = "Yes" Then Me.Range("J23").Value = "CAP" Else Me.Range("J23").Value = "" End If 'Set the Base Year Adj label If Range("B29").Value = "Yes" Then Me.Range("J24").Value = "Base Year Adj" Else Me.Range("J24").Value = "" End If 'Set the Minimum CAP Label If Range("B30").Value = "Yes" Then Me.Range("J25").Value = "Minimum CAP" Else Me.Range("J25").Value = "" End If End If ws_exit: Application.EnableEvents = True End Sub -- "Bob Phillips" wrote: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" and iCtr must be greater than 21 otherwise you get that problem of an invalid cell again. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Hi, Sorry, my insertion formula is My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" "Karen53" wrote: Hi I have been going around and around with this. I have finally isolated my problem as being the insertion formula. I suspect my problem is the formatting of either the Indirect, VLookup or the named range. Is there something special I need to do to insert these via code? Everything I've tried so far has not worked. Thanks My formula: =IF(ISBLANK($J36),"",IF($J36="No",0,INDIRECT(VLOOK UP('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect & Named Range
I am going to have to leave this until the morning Karen.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Bob, I feel like I am going nuts. This was working this morning. I saved it and worked in another part of the workbook which at this point is unrelated to this sheet. I go back to this sheet and this no longer works. I don't get what is happening. It is impossilble for ictr to be less than 21 as it starts at 36. I need to understand why this happened. I've gone through this again trying to find the problem. I've messed with it now and probalby made it worse but I can't see what is or was wrong. I'm getting pretty frustrated. Why would it work and then not work when I hadn't made any changes to this code? Here is the complete routine. Thanks, Karen Option Explicit ' ' 'Master CAM worksheet procedures Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B26" Dim iCtr As Long Dim sNo As String Dim sYes As String Dim sPercentYes As String Dim sPercentNo As String Dim LastRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then LastRow = 337 If Range("B26").Value = "Yes" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share formula sYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & _ "=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _ "C16,IF(ISBLANK(R3C2),(R" & iCtr & "C11* R" & iCtr & _ "C9),(R" & iCtr & "C11* R" & iCtr & "C9)/365*(R3C2)))))" Me.Range("L" & iCtr).FormulaR1C1 = sYes 'original formula =IF(ISBLANK($J36),"",IF($J36="No",0,IF(ISNUMBER($P 36),$P36,IF(ISBLANK($B$3),($K36*$I36),($K36*$I36)/365*($B$3))))) Next ElseIf Range("B26").Value = "No" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share formula sNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _ "IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _ "C16,IF(ISBLANK(R3C2),(R" & iCtr & "C11* R" & iCtr & _ "C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R3C2))))" Me.Range("L" & iCtr).FormulaR1C1 = sNo 'original formula =IF(ISBLANK($J36),"",IF(ISNUMBER($P36),$P36,IF(ISB LANK($B$3),($K36*$G36),($K36*$G36)/365*($B$3)))) Next End If End If If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then LastRow = 337 If Range("B26").Value = "Yes" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share Percentage - line items begin on row 15, so 36-15 = 21 sPercentYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" Me.Range("K" & iCtr).FormulaR1C1 = sPercentYes 'original formula =IF(ISBLANK(J36),"",IF(J36="No",0,INDIRECT(VLOOKUP ('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) Next ElseIf Range("B26").Value = "No" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share Percentage - line items begin on row 15, so 36-15 = 21 sPercentNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _ "Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" Me.Range("K" & iCtr).FormulaR1C1 = sPercentNo 'original formula =IF(ISBLANK(J36),"",INDIRECT(VLOOKUP('Line Items'!$C15,CAMPerCentLoc,3,FALSE))) Next End If End If If Not Intersect(Target, Me.Range("B28:B30")) Is Nothing Then 'Set the CAP label If Range("B28").Value = "Yes" Then Me.Range("J23").Value = "CAP" Else Me.Range("J23").Value = "" End If 'Set the Base Year Adj label If Range("B29").Value = "Yes" Then Me.Range("J24").Value = "Base Year Adj" Else Me.Range("J24").Value = "" End If 'Set the Minimum CAP Label If Range("B30").Value = "Yes" Then Me.Range("J25").Value = "Minimum CAP" Else Me.Range("J25").Value = "" End If End If ws_exit: Application.EnableEvents = True End Sub -- "Bob Phillips" wrote: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" and iCtr must be greater than 21 otherwise you get that problem of an invalid cell again. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Hi, Sorry, my insertion formula is My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" "Karen53" wrote: Hi I have been going around and around with this. I have finally isolated my problem as being the insertion formula. I suspect my problem is the formatting of either the Indirect, VLookup or the named range. Is there something special I need to do to insert these via code? Everything I've tried so far has not worked. Thanks My formula: =IF(ISBLANK($J36),"",IF($J36="No",0,INDIRECT(VLOOK UP('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Indirect & Named Range
Bob,
I got it to work. This time I saved it as a different workbook so I can't mess it up again. Thanks for your help -- Karen "Bob Phillips" wrote: I am going to have to leave this until the morning Karen. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Bob, I feel like I am going nuts. This was working this morning. I saved it and worked in another part of the workbook which at this point is unrelated to this sheet. I go back to this sheet and this no longer works. I don't get what is happening. It is impossilble for ictr to be less than 21 as it starts at 36. I need to understand why this happened. I've gone through this again trying to find the problem. I've messed with it now and probalby made it worse but I can't see what is or was wrong. I'm getting pretty frustrated. Why would it work and then not work when I hadn't made any changes to this code? Here is the complete routine. Thanks, Karen Option Explicit ' ' 'Master CAM worksheet procedures Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B26" Dim iCtr As Long Dim sNo As String Dim sYes As String Dim sPercentYes As String Dim sPercentNo As String Dim LastRow As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then LastRow = 337 If Range("B26").Value = "Yes" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share formula sYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & _ "=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _ "C16,IF(ISBLANK(R3C2),(R" & iCtr & "C11* R" & iCtr & _ "C9),(R" & iCtr & "C11* R" & iCtr & "C9)/365*(R3C2)))))" Me.Range("L" & iCtr).FormulaR1C1 = sYes 'original formula =IF(ISBLANK($J36),"",IF($J36="No",0,IF(ISNUMBER($P 36),$P36,IF(ISBLANK($B$3),($K36*$I36),($K36*$I36)/365*($B$3))))) Next ElseIf Range("B26").Value = "No" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share formula sNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _ "IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _ "C16,IF(ISBLANK(R3C2),(R" & iCtr & "C11* R" & iCtr & _ "C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R3C2))))" Me.Range("L" & iCtr).FormulaR1C1 = sNo 'original formula =IF(ISBLANK($J36),"",IF(ISNUMBER($P36),$P36,IF(ISB LANK($B$3),($K36*$G36),($K36*$G36)/365*($B$3)))) Next End If End If If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then LastRow = 337 If Range("B26").Value = "Yes" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share Percentage - line items begin on row 15, so 36-15 = 21 sPercentYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" Me.Range("K" & iCtr).FormulaR1C1 = sPercentYes 'original formula =IF(ISBLANK(J36),"",IF(J36="No",0,INDIRECT(VLOOKUP ('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) Next ElseIf Range("B26").Value = "No" Then For iCtr = 36 To LastRow 'update the Pro-Rata Share Percentage - line items begin on row 15, so 36-15 = 21 sPercentNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _ "Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" Me.Range("K" & iCtr).FormulaR1C1 = sPercentNo 'original formula =IF(ISBLANK(J36),"",INDIRECT(VLOOKUP('Line Items'!$C15,CAMPerCentLoc,3,FALSE))) Next End If End If If Not Intersect(Target, Me.Range("B28:B30")) Is Nothing Then 'Set the CAP label If Range("B28").Value = "Yes" Then Me.Range("J23").Value = "CAP" Else Me.Range("J23").Value = "" End If 'Set the Base Year Adj label If Range("B29").Value = "Yes" Then Me.Range("J24").Value = "Base Year Adj" Else Me.Range("J24").Value = "" End If 'Set the Minimum CAP Label If Range("B30").Value = "Yes" Then Me.Range("J25").Value = "Minimum CAP" Else Me.Range("J25").Value = "" End If End If ws_exit: Application.EnableEvents = True End Sub -- "Bob Phillips" wrote: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" and iCtr must be greater than 21 otherwise you get that problem of an invalid cell again. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karen53" wrote in message ... Hi, Sorry, my insertion formula is My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False))))" "Karen53" wrote: Hi I have been going around and around with this. I have finally isolated my problem as being the insertion formula. I suspect my problem is the formatting of either the Indirect, VLookup or the named range. Is there something special I need to do to insert these via code? Everything I've tried so far has not worked. Thanks My formula: =IF(ISBLANK($J36),"",IF($J36="No",0,INDIRECT(VLOOK UP('Line Items'!$C15,CAMPerCentLoc,3,FALSE)))) My insert formula: "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & "=""No""," & _ "0, Indirect(Vlookup('Line Items'!$R" & iCtr - 21 & "C3,CAMPerCentLoc,3,False)))" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using indirect to return a named range? | Excel Worksheet Functions | |||
| INDIRECT function to reference a named range | Excel Worksheet Functions | |||
| Sumproduct Indirect Named Dynamic Range using Offset | Excel Worksheet Functions | |||
| Indirect to Named range | Excel Worksheet Functions | |||
| How do I use indirect when referring to a named range in a closed | Excel Worksheet Functions | |||
Linear Mode
