Sometimes it works...sometimes it doesn't

[vincecodegreeen]

Hi,
I have a particularly perplexing issue.

The following code sometimes works and sometimes doesn't. I am using the Else If because the Select Case couldn't find it at all. The variable is exactly as the if statement, without the "". For instance '[CN caloriecalculator.xlsm]TDC'!$B$10 should match "'[CN caloriecalculator.xlsm]TDC'!$B$10" in the code.

Also, please note that I have commented out some steps in my effort to isolate the problem.
Thanks for helping,
Vince

Sub CellAddress()
'
' Find Cell Address Macro
'
Dim mealname As String
'Dim wkb As Workbook
'Dim ws As Worksheet
'Set wkb = ThisWorkbook




'For Each ws In wkb.Worksheets
'Select Case ws.Name
'Case "TDC", "Food Cals", "Unassigned foods"
' skip
'Case Else
'ws.Activate




Range("G47").Select
'For i = 1 To 4


    mealname = activecell.Value


    If mealname = "'[CN caloriecalculator.xlsm]TDC'!$B$9" Then
    activecell.Offset(0, -5).Value = "Meal 1, Protein"
    activecell.Offset(0, -1).Value = "G5"
    ElseIf mealname = "'[CN caloriecalculator.xlsm]TDC'!$B$10" Then
    activecell.Offset(0, -5).Value = "Meal 1, Carbs"
    activecell.Offset(0, -1).Value = "G6"
    ElseIf mealname = "'[CN caloriecalculator.xlsm]TDC'!$B$11" Then
    activecell.Offset(0, -5).Value = "Meal 1, Fats"
    activecell.Offset(0, -1).Value = "G7"
    ElseIf mealname = "'[CN caloriecalculator.xlsm]TDC'!$B$12" Then
    activecell.Offset(0, -5).Value = "Meal 1, Fr/Vegs"
    activecell.Offset(0, -1).Value = "G8"
    
    Else
    activecell.Offset(0, -5).Value = "None"
    End If
    activecell.Offset(1, 0).Select
    'Next i
    'End Select
    'Next ws
    
    End Sub

 

[BrianMH]

To clarify the actual value of the cell is '[CN caloriecalculator.xlsm]TDC'!$B$9? Not part the formula in the cell?

 

[vincecodegreeen]

Hi Brian,
Right. The formula is =IF(ABS(H47)>10,IF(H47>0,CELL("address",OFFSET(TDC!B9,0,MATCH(LARGE(IF(TDC!B9:H9>0,(TDC!B9:H9)),2),TDC!B9:H9,0)-1)),
CELL("address",OFFSET(TDC!B9,0,MATCH(SMALL(IF(TDC!B9:H9>0,(TDC!B9:H9)),2),TDC!B9:H9,0)-1))),
"-")
Tnx.

 

[BrianMH]

Ok. And what is the exact value of the cell? It doesn't look like that formula is pointing at another workbook so it wouldn't have the [CN caloriecalculator.xlsm] part in it.

 

[vincecodegreeen]

Hi Brian,
Even though this is the same workbook the [CN caloriecalculator.xlsm] part never stopped the recognition before. However, I think I may have stumbled on an answer, which raises another question if you would consider.
When I copied the function IF(ABS(H47)>10,IF(H47>0,CELL("address",OFFSET(TDC!B9,0,MATCH(LARGE(IF(TDC!B9:H9>0,(TDC!B9:H9)),2),TDC!B9:H9,0)-1)),
CELL("address",OFFSET(TDC!B9,0,MATCH(SMALL(IF(TDC!B9:H9>0,(TDC!B9:H9)),2),TDC!B9:H9,0)-1))), "-") into the VBE by F2 enter it converted it to a formulaR1C1. Like so--- IF(>0,CELL(""address"",OFFSET(TDC!R[-38]C[-5],0,MATCH(LARGE(IF(TDC!R[-38]C[-5]:R[-38]C[1]>0,(TDC!R[-38]C[-5]:R[-38]C[1])),RC[6]),TDC!R[-38]C[-5]:R[-38]C[1],0)-1))," & Chr(10) & "CELL(""address"",OFFSET(TDC!R[-38]C[-5],0,MATCH(SMALL(IF(TDC!R[-38]C[-5]:R[-38]C[1]>0,(TDC!R[-38]C[-5]:R[-38]C[1])),RC[6]),TDC!R[-38]C[-5]:R[-38]C[1],0)-1)))," & Chr(10) & """-"")" OK I thought that was fine but what I didn't realise was that the output was also in the R1C1 format; consequently the IF Then didn't recognise the string. If I revise the IF Then comparisons it works. But now the next step doesn't recognise the indirect value of the answer even though I use Range("E47").Select
activecell.FormulaR1C1 = "=INDIRECT(CELL(RC[2]). I get a value error.

Should I attempt to write the first function formulaR1C1 into an A1 and if so how?
Your help is greatly appreciated.
Vince

 

[BrianMH]

=INDIRECT(CELL(RC[2])

This is an invalid formula, you haven't told it what you return. Also you are using indirect to refer to the address of the cell you are pointing at. Since you are pointing at that cell you don't need to use indirect just use .Formula = "=CELL(""address"",RC[2])". This will return a standard address.

Basically you can use the R1C1 style of formula but assign it to the .formula instead of .formulaR1C1 and you will get the standard formula automatically.

 

[vincecodegreeen]

Hi Brian,
Thanks so much. This is now working.

Sub MacroA()
'
' MacroA Macro
Dim units As String
Dim wkb As Workbook
Dim ws As Worksheet
Dim mealname As String
Dim quantity As String
Set wkb = ThisWorkbook


Range("g47").Select


If activecell.Offset(0, 1) >= 10 Then

'Find meal to be adjusted
For i = 1 To 4
activecell.Offset(0, 7).Value = i
activecell.Formula = _
"=IF(RC[1]>0,CELL(""address"",OFFSET(TDC!R[-38]C[-5],0,MATCH(LARGE(IF(TDC!R[-38]C[-5]:R[-38]C[1]>0,(TDC!R[-38]C[-5]:R[-38]C[1])),RC[7]),TDC!R[-38]C[-5]:R[-38]C[1],0)-1))," & Chr(10) & "CELL(""address"",OFFSET(TDC!R[-38]C[-5],0,MATCH(SMALL(IF(TDC!R[-38]C[-5]:R[-38]C[1]>0,(TDC!R[-38]C[-5]:R[-38]C[1])),RC[7]),TDC!R[-38]C[-5]:R[-38]C[1],0)-1)))"


'Find its Cell Address


Call CellAddress
'Qualify quantity type
Range("g51").Select
activecell.Formula = "=INDIRECT(R[-4]C[-1])"
If activecell <> "units" Then
Exit For
End If
Next i

Range("e47").Select
activecell = "=SUM(INDIRECT(RC[2]),-RC[3])"

End If

End Sub

I really appreciate you help.
Vince