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

jimayers

Board Regular
Joined
Nov 14, 2010
Messages
99
Hi all - I have been recieving some help pertaining to finding empty rows. I got the code to work:
Code:
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:
Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

MUKESHY12390

Well-known Member
Joined
Sep 18, 2012
Messages
889
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
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
 
Last edited:

MUKESHY12390

Well-known Member
Joined
Sep 18, 2012
Messages
889
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
add worksheet ref in the beginning .

Code:
[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]
 
Last edited:

jimayers

Board Regular
Joined
Nov 14, 2010
Messages
99
add worksheet ref in the beginning .

Code:
[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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,695
Messages
5,626,357
Members
416,174
Latest member
LavendarRabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top