with no reply's I reworked the code but still get errors
I have several worksheets (at present 27) with or without data in row(3). Row(1), and row(2) values depend on if row(3) is blank; for example if D3 has data, then D1 will have 'Data from ID 2' and D2 will have 'Columm 4' and cells 4 to 304 in column(D) will have borders for more information. If AF3 is blank, then AF1 and AF2 will be blank and the cells in column AF will be without borders. The worksheets will vary the placement of the primary data BUT always in the 3rd row. I'm still a 'NEWBIE' with vba and so my looping skills are simple as you can see from my code attempt. I'm getting (Run-time error 1004, Application-defined or object-defined error) , and of course it does not say where. Any help will be appreciated.
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Re: Error with a loop across columns with an inside loop down rows
I tried both add-on 's but I still get the same error. I'll keep working on it thought. I'll keep the first suggestion about .column. I forgot about it. THANKS
Re: Error with a loop across columns with an inside loop down rows
May be:
PHP Code:
Option Explicit Sub Test() Dim lc&, cell As Range, ws As Worksheet For Each ws In Worksheets ' loop with each sheet in workbook 'If ws.name <> "Main" then ====> add this condition, if you want to exclude specific sheet "Main" lc = ws.Cells(3, Columns.Count).End(xlToLeft).Column ws.Range("A1", ws.Cells(2, lc)).ClearContents ' delete history in row 1 & 2 ws.Range(ws.Cells(1, lc + 1), ws.Cells(Rows.Count, Columns.Count)).Delete ' delete all data and border from last column For Each cell In ws.Range(ws.Cells(3, "A"), ws.Cells(3, lc)) ' loop from A3 to last cell with data in row 3 Select Case IsEmpty(cell) Case True cell.Offset(1, 0).Resize(304, 1).Borders.LineStyle = xlNone Case Else cell.Offset(-2, 0).Value = "Data from ID" & cell.Column - 2 cell.Offset(-1, 0).Value = "COLUMN" & cell.Column cell.Offset(1, 0).Resize(304, 1).Borders.LineStyle = xlContinuous End Select Next Next End Sub
Re: Error with a loop across columns with an inside loop down rows
bebo021999
I liked your code and it work but it works on ALL worksheets. My worksheets have a 2-part name with a separator (.) such as 'Data.All' and 'Data.Mathmatics' and 'Main.Switchboard' and 'Main.Collected' etc. Can this code run on only the worksheets with the name starting with Data.
Re: Error with a loop across columns with an inside loop down rows
Thanks for the help! At first had an error but then discovered extra speech mark after Data (If ws.name like "Data*""). Once it was remover code worked fine. AGAIN THANKS
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks