VBA Excel finding the string in the range doesn't work with CALL statement

Question

I would like to trigger my macro from another macro.

My first macro looks like this:

 Sub Basic()
 Dim wr As Worksheet
 Dim myDataObject As DataObject
 Set myDataObject = New DataObject
 Set wr = ThisWorkbook.Sheets("Data")

 myDataObject.GetFromClipboard
 If myDataObject.GetFormat(1) = True Then
 wr.Range("A1").PasteSpecial
 Else
 MsgBox ("Please provide the data")
 Exit Sub
 End If
 If wr.Range("A1").Value Like "*Ephemeris*" Then
 Call General
 ElseIf wr.Range("A1").Value Like "*Event*" Then
 Call Almanach
 ElseIf wr.Range("A1").Value Like "*Standstil*" Then
 Call Others
 Else
 MsgBox ("Please provide correct data")
 End If

from where I want to call another macro - Almanach.

The macro works in general, but I don't understand, why the following code is totally omitted:

   For Each Cell In work.Range("A5:A" & LastrouA)
   If InStr(1, Cell.Value, "FULL MOON") > 0 Then
   Cell.Interior.ColorIndex = 37
   End If
   Next Cell

Whereas it works (although quite slow) when I run this macro directly via console. I tried to get it around by creating the button, but the result is exactly the same! Is there some alternative to the InStr function or have I forgotten something?

The whole code works except these lines only.

 For Each Cell In work.Range("A3:A" & LastrouA)
 If InStr(1, Cell.Value, "FULL MOON") > 0 Then
    Cell.Interior.ColorIndex = 37
ElseIf InStr(1, Cell.Value, "0.") > 0 Then
        Cell.Interior.ColorIndex = 17
ElseIf InStr(1, Cell.Value, "Elong") > 0 Then
        Cell.Interior.ColorIndex = 36
ElseIf InStr(1, Cell.Value, "Shower") > 0 Then
        Cell.Interior.ColorIndex = 40
End If
If InStr(1, Cell.Value, "Eclipse") > 0 Then
        Cell.Interior.ColorIndex = 38
End If
Next Cell
For Each Cell In work.Range("B3:B" & LastrouA)
If InStr(1, Cell.Value, "FULL MOON") > 0 Then
        Cell.Interior.ColorIndex = 37
ElseIf InStr(1, Cell.Value, "0.") > 0 Then
        Cell.Interior.ColorIndex = 17
ElseIf InStr(1, Cell.Value, "Elong") > 0 Then
        Cell.Interior.ColorIndex = 36
ElseIf InStr(1, Cell.Value, "Shower") > 0 Then
        Cell.Interior.ColorIndex = 40
End If
If InStr(1, Cell.Value, "Eclipse") > 0 Then
        Cell.Interior.ColorIndex = 38
End If
 Next Cell

 For Each Cell In work.Range("A3:A" & LastrouA)
 If InStr(1, Cell.Value, "Jan") > 0 Then
        Cell.Offset(-1, 0).Value = "January"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Feb") > 0 Then
        Cell.Offset(-1, 0).Value = "February"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Mar ") > 0 Then
        Cell.Offset(-1, 0).Value = "March"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Apr") > 0 Then
        Cell.Offset(-1, 0).Value = "April"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "May") > 0 Then
        Cell.Offset(-1, 0).Value = "May"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Jun") > 0 Then
        Cell.Offset(-1, 0).Value = "June"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 End If
 Next Cell

 For Each Cell In work.Range("B3:B" & LastrouA)
 If InStr(1, Cell.Value, "Jul") > 0 Then
        Cell.Offset(-1, 0).Value = "July"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Aug") > 0 Then
        Cell.Offset(-1, 0).Value = "August"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Sep ") > 0 Then
        Cell.Offset(-1, 0).Value = "September"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Oct") > 0 Then
        Cell.Offset(-1, 0).Value = "October"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Nov") > 0 Then
        Cell.Offset(-1, 0).Value = "November"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 ElseIf InStr(1, Cell.Value, "Dec") > 0 Then
        Cell.Offset(-1, 0).Value = "December"
        Cell.Offset(-1, 0).Font.Bold = True
        Cell.Offset(-1, 0).HorizontalAlignment = xlCenter
 End If
 Next Cell

I see only one result from the loop as per in the screenshot below:

Not enough information here to reproduce: is `work` the correct worksheet? — BigBen, Feb 18 '21 at 15:26
Yes, everything is happening on the "Data" worksheet, which has been definied. — Geographos, Feb 18 '21 at 15:28
Your code snippets don't reflect that. What's the value of `LastrouA`? You need to use `Debug.Print` and other debugging methods.. — BigBen, Feb 18 '21 at 15:28
I've expanded my full loops and image as the result. Only one comes I don't know why — Geographos, Feb 18 '21 at 15:32
What's the value of `LastrouA`? Again, you need to use `Debug.Print` on your end. — BigBen, Feb 18 '21 at 15:33
[Here's how to find the last row correctly](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). But, did you use `Debug.Print LastrouA` and check the output in the Immediate Window? — BigBen, Feb 18 '21 at 15:39
OK I used Debug.Print. It was a tad better. However the best solution by now was a manual adding of the ces beyond the last row: LastrouB = work.Range("B5").End(xlDown).Row + 150 Then everything started to work perfect. — Geographos, Feb 18 '21 at 16:11
You should use `End(xlUp)` to find the last row. Please read the link I provided — BigBen, Feb 18 '21 at 16:14

VBA Excel finding the string in the range doesn't work with CALL statement

0 Answers0