Need help with an excel macro im trying to make

[Ian Durst]

Hey guys I have a very limited knowledge in visual basic and i need some
help..........I would like some sort of macro that will take the data in
an entire row that I specify and copy that row and paste that exact row
and format into each row below it that contains a certain value in a
column............for example:

I have an excel sheet and in row 1 I have a lot of data, I need to be
able to search all of the data in the rows below it for the letter "D"
and every cell in the A colomn that has the letter "D" I need to paste
the data from Row 1 into that any row below it containing "D".......but
the rows also contain the letters "F" "G" "H" and I dont want the macro
pasting row 1 into any of those other rows that contain any other
letters.....there is around 6000 rows in all and I really do not want to
do each individually by hand and my visual basic skills are slim to
none.....hope you guys can help...........thanx

*** Sent via Developersdex http://www.developersdex.com ***

 

[JLGWhiz]

You were doing pretty good until you got to this:

".......but
the rows also contain the letters "F" "G" "H" and I dont want the macro
pasting row 1 into any of those other rows that contain any other
letters.....

For i = 1 To 6000
If Cells(i, 1) = "D" Then
Range("A1").EntireRow.Copy Range("A" & i)
End If
Next

This snippet will walk down column A for 6000 rows and find every cell with
"D" in it, copy row 1 and paste it to the row where "D" was found, but it
overwrites whatever is in the row that is pasted to.

If that is not what you want, you need to be specific about what you want to
copy and where you want to put it.

 

[Joel]

The code below does exactly what you asked for. It probably need some
modifications to work under all conditions.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/15/2007 by Joel
'
Const finddata = "D"
'
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Set copyrange = Cells(1, "A").EntireRow

RowCount = 2
Do While RowCount <= LastRow
If Cells(RowCount, "A") = finddata Then
RowCount = RowCount + 1
copyrange.Copy
Range("A" & CStr(RowCount)).Insert Shift:=xlDown

End If
RowCount = RowCount + 1
Loop

End Sub

 

[JLGWhiz]

I was not sure how to interpret that copy destination. Good job, Joel.

 

[VB MacroMan]

I ran the macro on the spreadsheet I had and I specified for it to
search for all of the rows containing the letter "D" in the A column and
told it to paste row A4 into it, it runs the macro and takes an really
long time for it to complete.....like 30 min almost and I start with
6000 rows and end with 9000.......im not sure what it is doing but it
doesnt work right.......any help??????

Durst

*** Sent via Developersdex http://www.developersdex.com ***