[ksquirt]

I need a line in a macro to search column D, find value '90601' and copy the row that has that value in column to a new row and change '90601' to another value. There will be multiple rows with this value, but the other columns will contain other information. Can anybody help with this? I have attached the spreadsheet I am working with. Thanks!!

[ksquirt]

New value needs to be 20150

Originally Posted by ksquirt

I need a line in a macro to search column D, find value '90601' and copy the row that has that value in column to a new row and change '90601' to another value. There will be multiple rows with this value, but the other columns will contain other information. Can anybody help with this? I have attached the spreadsheet I am working with. Thanks!!

[ksquirt]

We have a macro that adds a row, so I am starting with it and I need to tweak it. Here's what I have so far (I figured with a lot of views I should keep you updated as to what I'm doing). What I'm looking at now is how to cycle thru all the rows as the amount of rows will change. I also need to paste the new number in column D. I have not tested this yet as it is in a larger macro.

Range("D8").Select

If ActiveCell.Value = "90601" Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Selection.EntireRow.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Else

Originally Posted by ksquirt

I need a line in a macro to search column D, find value '90601' and copy the row that has that value in column to a new row and change '90601' to another value. There will be multiple rows with this value, but the other columns will contain other information. Can anybody help with this? I have attached the spreadsheet I am working with. Thanks!!

[ksquirt]

After more searching thru this site and others here is my current piece of the macro. The problem I'm running into now is that I need certain cell removed. Right now it's removing the whole column. How do I get it to just remove the 'dollars' for the rows with 21050 in column D? and the 'hours'for all other rows? See attached spreadsheet.

Dim LSearchRow As Integer
LSearchRow = 6


While Len(Range("A" & CStr(LSearchRow)).Value) 0

'If Column D = "90601", copy entire row
If Range("D" & CStr(LSearchRow)).Value = "90601" Then
Range("D" & CStr(LSearchRow)).EntireRow.Copy
'Insert copied Row below the original
Range("D" & CStr(LSearchRow)).EntireRow.Insert Shift:=xlDown
'Change values in Columns D on new row to 20150
Range("D" & CStr(LSearchRow + 1)) = "20150"

End If


'If Column D = "20150", remove dollars columns
If Range("D" & CStr(LSearchRow)).Value = "20150" Then
Range("G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA: AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK,AM:AM,AO:AO,AQ:AQ ,AS:AS,AU:AU,AW:AW,AY:AY").Cell.Delete

'Selection.Delete Shift:=xlToLeft
End If
'If Column D does not equal "20150", remove hours columns (*using ' to get part of the code to not work while I focus on other parts*)
'If Range("D" & CStr(LSearchRow)).Value < "20150" Then
' Range("F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z ,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,A R:AR,AT:AT,AV:AV,AX:AX,AZ:AZ").Select
' Selection.Delete Shift:=xlToLeft

'End If
'Increment Search row variable
LSearchRow = LSearchRow + 1

'Loop
Wend