Excel Macro Help Please

Solved/Closed
mathisjr - Jun 19, 2008 at 08:43 PM
 rizvisa1 - Jul 7, 2010 at 08:02 AM
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 Sub
Related:

23 responses

Hello,

Can any please help me how do I search for a specific cell with data like "Name" and paste the data after that row into a new column? How do I count the row after "Name" cell like "Name"+1 and then "Name" + 2

such as if cell = "Name"
copy the cell(row) after that
paste into Column N


Thanks!
-1
Ivan-hoe Posts 433 Registration date Saturday February 16, 2008 Status Member Last seen October 17, 2008 110
Jul 21, 2008 at 12:52 AM
Hello,
if you only need to determine if there is any value entered in column A, use the function CountA

Sheets("List").Activate
If Application.WorksheetFunction.CountA(Columns("A")) = 0 Then
Exit Sub
Else
'instructions
End If

If you absolutely need to work down column A to see if there is anything entered in each cell, then use For ... Next

Dim LastLine As Integer, i As Integer

Sheets("List").Activate
LastLine = Cells(Columns("A").Rows.Count, 1).End(xlUp).Row

For i = 1 To LastLine
If Not Range("A" & i).Value = Empty Then
'instructions
End If
Next i

Ivan
-1
Ivan,
I have a macro question: Column A has either "active" or blank cells, Column B has "Inactive" or blank cells next to the actives in column A. I want to move all "active" cells from column A to B and leave the Inactives untouched.
0
Ivan-hoe Posts 433 Registration date Saturday February 16, 2008 Status Member Last seen October 17, 2008 110 > Savage
Jul 23, 2008 at 01:31 AM
Hello savage,
please create a new thread to ask your question (by clicking on "Ask your question")
thank you
0
Hi,

I have a similar problem in Excel VBA.
I have an entry with daily variable data, so today I have 1000 entries and 1200 the next day. With most of this input data I do some calculations. What I need to do is make a macro that copies these calculation formulas (AE5:FB5) for the new 200 entries.

I got to this script below, I place the cursor on line 1001, column AE, but I have to move the active cell down after a copy. I could also have copied the (AE:FB from the last line) because I already have 1000 entries and formulas. Maybe theres a smatter way to do it, but I'm a begginer in Excel macros.

Thanks for your help

Sub copy()

Dim Bottom As Long
Dim FinalRow As Long
Dim temp As Long


FinalRow = Range("A65536").End(xlUp).Row
CurrentRow = ActiveCell.Row
MsgBox FinalRow & " " & CurrentRow

Range("AE5:fb5").copy
For temp = CurrentRow To FinalRow
ActiveCell.PasteSpecial
ActiveCell.Select = ActiveCell.Row + 1 <--------- WRONG

MsgBox ActiveCell.Rows
Next temp

end sub
0
Hi Ivan,

I'm creating a leave card system for 68 staff members.
Each staff member has a worksheet detailing Annual Leave entitlement, a record of requests resulting in remaining days which feeds into a central sheet listing all employees with leave entitlements and remainders. The central page hyperlinks into the relevant sheets - great.

I now want to add sick leave into the individual sheets and create columns in the central sheet not only totalling 'sick days taken to date' but also 'periods of sick to date' which I want to add up cells containing data only, within a given range (which will be taken from the individual relevant sheet, 'sick start date column' probably). The result I want is that if an employee has taken 4 periods of sick, the central sheet will display '4' against his/her name.

Is this possible?

Using excel 2007

Would be really grateful xx
0
Hi Sir,

I want to copy values in the range A1:I1 to every alternate row.. ie to A3: I3, A5 :I5 .. there are almost 200 rows are there.


Thanks
Jinta
-1
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Aug 11, 2008 at 04:30 AM
Hello,
If you need help, it would be great to create you own thread for your question. It allows a best following of your problem which is not exactly the same as mathisjr. It also allows to other people who read the thread a best understanding.
Thanks.
Best regards
0