Code working by itself, BUT not when "call"ed by another code

[jimayers]

Hi all - I have been recieving some help pertaining to finding empty rows. I got the code to work:

Dim nLastrow, bFind As Range
Dim ii As Integer
For ii = 2 To 5
    nLastrow = Cells(Rows.Count, 3).End(xlUp).Row
    Set bFind = ActiveSheet.Range("C5:" & "C" & nLastrow).Find(What:="B" & ii, LookIn:=xlValues, LookAt:=xlPart)
    If bFind Is Nothing Then GoTo a:
    If WorksheetFunction.CountA(bFind.Offset(-1, 0).Resize(1, 3)) = 0 Then
        MsgBox ("CountA worked")
        GoTo a:
    End If
    If Len(Join(Application.Index(Rows(bFind.Row).Value, 1, 0), "")) = 0 Then
        MsgBox ("Len / Join code worked")
        GoTo a:
    End If
    bFind.EntireRow.Insert 'shift:=xlDown
a:
    Rows(bFind.Row - 1).RowHeight = 9
    bFind.Offset(-1, -2).Resize(1, 3).Interior.ColorIndex = 15
Next ii

But when I "call" this code out from another code (macro), neither the CountA line or the Len(Join...) line work. The following is the code where it is called from:

Sub lookUp_ALL_in_Column2()
Application.ScreenUpdating = False
Dim pFind, c As Range
Dim Lastrow As Long
Dim i As Integer
'clearing cell colors
Range(Cells(1, "A"), Cells(Rows.Count, "E")).Cells.Interior.Color = xlNone
'clearing cell borders
With Range(Cells(5, "A"), Cells(Rows.Count, "C").End(xlUp)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.RowHeight = 18
End With
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B:B").Interior.ColorIndex = 0
'Finding values in Row(2) -- C# --  and copying correct name, c#, and housing to the activesheet
For i = 5 To Lastrow
    Set c = Cells(i, 2)
    If c <> "" Then
        Set pFind = Sheets("SMs").Range("E:E").Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole) 'setting value to find which is a c#
            If pFind Is Nothing Then
            c.Offset(, -1).Interior.ColorIndex = 6 'if no number is found then this highlights name field
            GoTo B:
            End If
        c.Offset(0, -1).Value = pFind.Offset(0, -4) 'this sets the name found in sheets("SMs")
        c.Value = pFind.Value 'this sets the c# found in sheets("SMs")- this is a redundancy, but for now copies the font formating
        c.Offset(0, 1).Value = pFind.Offset(0, 6) 'this sets the housing found in sheets("SMs")
    Else
        If c = "" Then GoTo B:
        If pFind Is Nothing Then c.Offset(, -1).Interior.ColorIndex = 6
    End If
B:
Next
'Sort alphabetically by Housing
    ActiveWorkbook.ActiveSheet.Range("C5:C" & Lastrow).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("C5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A6:C" & Lastrow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
Call Simplify_HousingNumber_Name
Call Insert_Blank_Rows_andBORDER
Range("A2").Activate
Application.ScreenUpdating = True
End Sub

I hope this is not to much, but I am also hoping someone with a good eye can spot my obvious mistake and doesn't have to pour through my code. I would like to learn and any help is appreciated. Thanks to those who have already helped to get me this far!
Thanks for your help

 

[MUKESHY12390]

in your code worksheet info is missing everywhere. like cells(rows.count,3).end(xlup).row instead of worksheets("NameHere").cells(rows.count,3).end(xlup).row

you need to mention worksheet name.

Dim nLastrow, bFind As Range
Dim ii As Integer
For ii = 2 To 5
nLastrow = Cells(Rows.Count, 3).End(xlUp).Row
Set bFind = ActiveSheet.Range("C5:" & "C" & nLastrow).Find(What:="B" & ii, LookIn:=xlValues, LookAt:=xlPart)
If bFind Is Nothing Then GoTo a:
If WorksheetFunction.CountA(bFind.Offset(-1, 0).Resize(1, 3)) = 0 Then
MsgBox ("CountA worked")
GoTo a:
End If
If Len(Join(Application.Index(Rows(bFind.Row).Value, 1, 0), "")) = 0 Then
MsgBox ("Len / Join code worked")
GoTo a:
End If
bFind.EntireRow.Insert 'shift:=xlDown
a:
Rows(bFind.Row - 1).RowHeight = 9
bFind.Offset(-1, -2).Resize(1, 3).Interior.ColorIndex = 15
Next ii

 

[MUKESHY12390]

add worksheet ref in the beginning .

[COLOR=#333333]Dim nLastrow, bFind As Range
Dim ii As Integer

worksheets("TypeNameHere").activate    '''''' change name here
For ii = 2 To 5
    nLastrow = Cells(Rows.Count, 3).End(xlUp).Row
    Set bFind = ActiveSheet.Range("C5:" & "C" & nLastrow).Find(What:="B" & ii, LookIn:=xlValues, LookAt:=xlPart)
    If bFind Is Nothing Then GoTo a:
    If WorksheetFunction.CountA(bFind.Offset(-1, 0).Resize(1, 3)) = 0 Then
        MsgBox ("CountA worked")
        GoTo a:
    End If
    If Len(Join(Application.Index(Rows(bFind.Row).Value, 1, 0), "")) = 0 Then
        MsgBox ("Len / Join code worked")
        GoTo a:
    End If
    bFind.EntireRow.Insert 'shift:=xlDown
a:
    Rows(bFind.Row - 1).RowHeight = 9
    bFind.Offset(-1, -2).Resize(1, 3).Interior.ColorIndex = 15
Next ii

[/COLOR]

 

[jimayers]

add worksheet ref in the beginning .

[COLOR=#333333]Dim nLastrow, bFind As Range
Dim ii As Integer

worksheets("TypeNameHere").activate    '''''' change name here
For ii = 2 To 5
    nLastrow = Cells(Rows.Count, 3).End(xlUp).Row
    Set bFind = ActiveSheet.Range("C5:" & "C" & nLastrow).Find(What:="B" & ii, LookIn:=xlValues, LookAt:=xlPart)
    If bFind Is Nothing Then GoTo a:
    If WorksheetFunction.CountA(bFind.Offset(-1, 0).Resize(1, 3)) = 0 Then
        MsgBox ("CountA worked")
        GoTo a:
    End If
    If Len(Join(Application.Index(Rows(bFind.Row).Value, 1, 0), "")) = 0 Then
        MsgBox ("Len / Join code worked")
        GoTo a:
    End If
    bFind.EntireRow.Insert 'shift:=xlDown
a:
    Rows(bFind.Row - 1).RowHeight = 9
    bFind.Offset(-1, -2).Resize(1, 3).Interior.ColorIndex = 15
Next ii

[/COLOR]

Will "activesheet" work? This code is embedded into a template which is copied and the user names new sheet.