Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup : Syntax Error
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 --------------- Also...is it possible to unhide the rows where the value of the N# cell has been changed from "0" without having to unhide all the rows in my first step??? Would REALLY APPRECIATE any help! -- THANKS! Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the syntax for referencing another sheet with 'lookup' | Excel Worksheet Functions | |||
MS Query import from Excel - Syntax Error | Excel Discussion (Misc queries) | |||
what is a syntax error in microsoft acess | Charts and Charting in Excel | |||
Formula syntax error - chinese and gibberish | Excel Discussion (Misc queries) | |||
Vlookup Syntax Error | New Users to Excel |