PDA

View Full Version : Need assistance with merging rows, please.



doctortt
11-28-2013, 06:28 PM
Hi, can you please take a look at the screenshot and provide me some insight on this?

If you look at row 2 to row 7, the Project ID is the same. How do I use VBA to fill the blank cells with S1 in column B and 2406269 in column C?

Then if you look at row 8 to 19, the Project ID is the same as well. Now I need to fill those blank cells with S1 in column B and 281252 in column c? (Sometimes, quarter can be S2, but this screenshot doesn't include it.)

Thank you very much.

SamT
12-01-2013, 05:49 PM
Sub SamT()
Dim Rw as Long
Dim IDStartRow As Long
Dim IDEndRow As Long
Dim QTRValue as String
Dim AMTValue as Long

IDstartRow = 2

Do While Cells(1, IDStartRow).Value <> ""
IDEndRow = IDStartRow
'Get the real IDEndRow number
Do until Cells(1, IDEndRow).Offset(1, 0).Value <> Cells(1, IDStartRow).Value
IDEndRow = IDEndRow + 1
Loop

'Find the QTR value
For Rw = IDStartRow to IDEndRow
If Cells(Rw, 2).Value <> "" Then
QTRValue = Cells(Rw, 2).Value
AMTValue = Cells(Rw, 3).Value

'assign the values here to insure a blank range will not be filled with false data.
Range(Cells(2, IDStartRow), Cells(2, IDEndRow)).Value = QTRValue
Range(Cells(3, IDStartRow), Cells(3, IDEndRow)).Value = AMTValue

Exit For 'Or Goto label after Next Rw. See next comment.
End If
Next Rw

IDStartRow = IDEndRow + 1
End While ' Or Loop while, I forget and My "office" computer is down.

End Sub

doctortt
12-02-2013, 06:17 PM
10910

Hi Sam. Thanks for your reply. I ran your codes, but the values were copied to the right. Please take a look at the screenshot. I'd appreciate if you could assist.

SamT
12-02-2013, 11:38 PM
:banghead::banghead::banghead::banghead::banghead:

Change these two lines
Range(Cells(2, IDStartRow), Cells(2, IDEndRow)).Value = QTRValue
Range(Cells(3, IDStartRow), Cells(3, IDEndRow)).Value = AMTValue

To
Range(Cells(IDStartRow, 2), Cells(IDEndRow, 2)).Value = QTRValue
Range(Cells(Cells(IDStartRow, 3), Cells(IDEndRow, 3)).Value = AMTValue