Lookup : Syntax Error

S

Steve_n_KC

I need sheet 1 N6:N55 to be populated with the text from sheet 2 Rows 4-53
under columns C thru N dependent upon sheet 1 B5 matching one of the values
in C1 thru N1 on sheet 2 (sheet 1 B5 value is a formula reference from those
same C-N1 cells on sheet 2 so there will always be an exact match if it makes
a difference)

Contents of the newly acquired N6:N55 values are then used to Hide/UnHide
Rows 6:55 on sheet 1. What I have now does what I want it to BUT I will have
to maintain 12 different macros and assign them to objects...I'm hoping to be
able to have a single macro that will work on the active sheet when run from
keyboard.

Current code...
ActiveSheet.Unprotect Password:="'"

Rows("6:57").Select
Selection.EntireRow.Hidden = False

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 6
EndRow = 56
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "n").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "n").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "n")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "n"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
Range("B5").Select
ActiveSheet.Protect Password:="'"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top