Sometimes it works...sometimes it doesn't

vincecodegreeen

New Member
Joined
Jan 17, 2014
Messages
39
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
Code:
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
 
Last edited by a moderator:

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
To clarify the actual value of the cell is '[CN caloriecalculator.xlsm]TDC'!$B$9? Not part the formula in the cell?
 

vincecodegreeen

New Member
Joined
Jan 17, 2014
Messages
39
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

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
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

New Member
Joined
Jan 17, 2014
Messages
39
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

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
=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

New Member
Joined
Jan 17, 2014
Messages
39
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
 

Forum statistics

Threads
1,082,385
Messages
5,365,151
Members
400,825
Latest member
Sreekanth_21

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top