0 votes
in VBA by Beginner (21 points)
Sub Get_Doc_ID_For_Sql_213()

' Get_Doc_ID_For_Sql Macro

Dim LastRow_A As Long
    With Sheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "B").End(xlDown).row
    End With
    
    If IsEmpty(Range("B2")) = False Then
        Sheets("213").Select
        Range("G2").Select
        ActiveCell.FormulaR1C1 = "=CONCATENATE(,RIGHT(RC[-5],8))"
        
        Else
            Call Get_Doc_ID_For_Sql_214
        
    End If
    
        If IsEmpty(Range("B3")) = False Then
            Sheets("213").Select
            Range("G3").Select
            ActiveCell.FormulaR1C1 = "=CONCATENATE("","",RIGHT(RC[-5],8))"
        End If
    
            If IsEmpty(Range("B4")) = False Then
                Sheets("213").Select
                Range("G3").Select
                Selection.AutoFill Destination:=Range("$G$3:G" & Range("B" & Rows.Count).End(xlUp).row)
                Range(Selection, Selection.End(xlDown)).Select
            End If
    
End Sub

In the excel sheet I have something that looks like this (which in column B)

(20190702_2_2227295_12447314)
I am trying to concatenate the last 8 numbers but when there is no data it stops. For Example I have 10 pages each with it's own data and the pages/sheets are called 210 through 220.

210 through 212 run fine and I get the info needed but once 213 runs it stops there and it won't run 214 - 220.

1 Answer

0 votes
by Super Expert (2.4k points)
selected by
 
Best answer

It looks like Get_Doc_ID_For_Sql_214 is only called when cell B2 is empty. That's the only conditional that has an Else statement after the VBA IsEmpty function. Without more information, I'm guessing your spreadsheet is stopping at 213 because cell B2 in that spreadsheet isn't empty so it never gets to the Else statement calling Get_Doc_ID_For_Sql_214

by Beginner (21 points)

Thank you for your reply, I have only learned about macros and VBA within the last week and a half so forgive illiteracy . So in this instance B2 should be empty and should move on to the next sheet and run the same code for 214 to get the data for sql.

ex:

so as you can see there is no data at all on 213 and it should move onto 214 etc. and this is the same code for 210 - 220 and since this data changes from month to month I need that variable in the code to just run the next page in case there is no data to concatenate.

 

by Super Expert (2.4k points)

If you add a new line and type the word Stop before the Call Get_Doc_ID_For_Sql_214 line, does your Macro stop when you run it? If it does, you may not have a macro named Get_Doc_ID_For_Sql_214 or it's coded incorrectly. If it doesn't cell B2 might not really be empty. You can try changing the If IsEmpty(Range("B2")) = False Then line to this and see if it produces a different result:

If Application.Clean(Trim(Range("B2").Value)) = "" Then

If your macro did properly stop when you added Stop line, you can press F8 to step through the code from that point on and you could identify where it's failing.

by Beginner (21 points)
+1

Thank you for the quick reply, I was able to find the error using your (Stop) method and I found out that I did not specify the sheet it was looking at and so it was trying to do it off the previous sheet here is the new code that work perfectly!!!! once again thank you.

Sub Get_Doc_ID_For_Sql_213()

' Get_Doc_ID_For_Sql Macro

Dim LastRow_A As Long
    With Sheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "B").End(xlDown).row
    End With
    
    If IsEmpty(Sheets("213").Range("B2")) = True Then
        Call Get_Doc_ID_For_Sql_214
        
        ElseIf IsEmpty(Sheets("213").Range("B2")) = False Then
            Sheets("213").Select
            Range("G2").Select
            ActiveCell.FormulaR1C1 = "=CONCATENATE(,RIGHT(RC[-5],8))"
        
            ElseIf IsEmpty(Sheets("213").Range("B3")) = False Then
                Sheets("213").Select
                Range("G3").Select
                ActiveCell.FormulaR1C1 = "=CONCATENATE("","",RIGHT(RC[-5],8))"
    
                ElseIf IsEmpty(Sheets("213").Range("B4")) = False Then
                    Sheets("213").Select
                    Range("G3").Select
                    Selection.AutoFill Destination:=Range("$G$3:G" & Range("B" & Rows.Count).End(xlUp).row)
                    Range(Selection, Selection.End(xlDown)).Select
                End If
    
End Sub

 

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...