mathisjr
06-19-2008, 07:58 PM
Hello Everyone,
I am sking for some help with an Excel macro please.
I have a 1,900 hundred static HTML pages that I have converted to text for the purpose of importing to a DB via an Excel CSV file. I have everything sitting in an excel file now with 1,900 hundred rows of nearly identical data (basically label, data, label, data, etc.) with the differences being some could have 5 entries in a row some could have 50. In about half the rows is a label "originator" with a name in the next adjoining cell, not all are in the same colum. I need to go through the file and move the label "originator" and its adjacent "name" to a defined column in my spreadsheet (i've chose 60) so that I have all of the "originator" entrise in the same column. My code works except when I hit a row that has no "originator" I then get "Run-Time error'91': Object variable or With block variable not set"
Could someone please help me to figure out how to skip rows without "originator"?
Thanks,
Joe
Here is my code:
Sub Macro1()
'
Dim r As Integer
Dim c As Integer
Dim x As Integer
totalrows = ActiveSheet.UsedRange.Rows.Count
For r = 1 To totalrows
Rows(r).Select
Selection.Find(What:="originator", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
x = r - 1
c = 60
Range("A1").Offset(x, c).Select
ActiveSheet.Paste
Next
End Sub
I am sking for some help with an Excel macro please.
I have a 1,900 hundred static HTML pages that I have converted to text for the purpose of importing to a DB via an Excel CSV file. I have everything sitting in an excel file now with 1,900 hundred rows of nearly identical data (basically label, data, label, data, etc.) with the differences being some could have 5 entries in a row some could have 50. In about half the rows is a label "originator" with a name in the next adjoining cell, not all are in the same colum. I need to go through the file and move the label "originator" and its adjacent "name" to a defined column in my spreadsheet (i've chose 60) so that I have all of the "originator" entrise in the same column. My code works except when I hit a row that has no "originator" I then get "Run-Time error'91': Object variable or With block variable not set"
Could someone please help me to figure out how to skip rows without "originator"?
Thanks,
Joe
Here is my code:
Sub Macro1()
'
Dim r As Integer
Dim c As Integer
Dim x As Integer
totalrows = ActiveSheet.UsedRange.Rows.Count
For r = 1 To totalrows
Rows(r).Select
Selection.Find(What:="originator", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
x = r - 1
c = 60
Range("A1").Offset(x, c).Select
ActiveSheet.Paste
Next
End Sub