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()
'
' Macro1 Macro
'
Dim r As Integer
Dim c As Integer
Dim x As Integer
' Count the number of data rows in the table
totalrows = ActiveSheet.UsedRange.Rows.Count
c = 60
For r = 1 To totalrows
x = r - 1
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
Range("A1").Offset(x, c).Select
ActiveSheet.Paste
Next
End SubConfiguration: Windows XP Internet Explorer 7.0
Hi mathisjr,
Sub Macro1()
Dim r As Integer
Dim c As Integer
Dim x As Integer
'***
Dim MyRange As Range
' Count the number of data rows in the table
totalrows = ActiveSheet.UsedRange.Rows.Count
c = 60
For r = 1 To totalrows
x = r - 1
Rows(r).Select
'***
Set MyRange = Selection.Find(What:="originator", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'to be deleted
' Selection.Find(What:="originator", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'***
If Not MyRange Is Nothing Then
Range(MyRange, MyRange.End(xlToRight)).Select
'to be deleted
'ActiveCell.Select
'Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
Range("A1").Offset(x, c).Select
ActiveSheet.Paste
'***
End If
Next
End Sub
I hope it helps. Ivan
|
I am trying to write a macro that copies data from a range and pastes it in the next open row in a different sheet.
|
Reply to Ivan-hoe
|
Good morning,
|
Ivan,
|
Hello,
|
Reply to jinta
|
Hi Ivan,
|
Ok, i don't know if someone can help me out, this is really simple i am sure. i have a sheet with different occupancies for hot water calculation. i am trying to set up a formula that if i enter a value in a column, it will then run through my calculations and return the total to my total row in that specific column. so for instance, in column b, if i enter 1 in for a shower, it goes through, multiplies out and gives me my 30 FU for hot water in L26. i then want the answer from L26 to be in B26. I only want the answer to show up in the corresponding column based on if there is any value in that column. so that C26 through K26 still show 0.
|
Hiii joe
|
Hello Everyone,
|
Hey there
|
Hello-
|
Can you please provide me with a macro code where a required inputed value in cell A1 of sheet 1 is searched from data in sheet 2, sheet 3, sheet 4 and sheet 5 and then the entire row corresponding to the searched value in any of the sheets 2, 3, 4, 5 is copy pasted in cell E1 of sheet 1. If the inputed value in cell A1 of sheet 1 is not found in any of the sheets 2, 3, 4, or 5 then cell f1 of sheet 1 will contain the text "Not available in database". |
Hi Ivan,
|
Excel macro "with[" end Excel macro combine sheets not blank rows Excel macro convert number to date Excel macro copy sheet to new worksheets based on value change in a column Excel macro path in a formula Excel macro reference cell Excel macro replace string Excel macro select text Excel macro to hide rows with zero value Excel macro to search a worksheet for data and copy matching rows to another worksheet Excel macro what row is excel calculating? Find file extension in excel macro How to change the caption of a command button in excel macro How to lookup cell contents in excel macro Importar csv a excel macro Made chart excel macro Rupees to words in excel macro Super excel macro