PDA

View Full Version : Excel Macro Help Please



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

JimmyTheHand
06-19-2008, 10:01 PM
Hello Joe, welcome to VBAX :hi:

Here's what you should do:
- Declare a variable of Range type
- Assign the result of Find to this variable
- Check if variable is Nothing

I think you are capable of updating the code based on these directions.

Additional remarks:
- In 99% of cases Select and Activate methods are not necessary.
- With setting c to 60 you get the results in column #61.

HTH

Jimmy