VBA MACRO-Copy BLANK/EMPTY Cell and PASTE SPECIAL ADD to an entire COLUMN by COLUMN HEADER NAME

[LittleZenGirl]

Hi All,
I'm trying write a MACRO to do the following (As a small porti of a large MACRO project):

(Consider that I will have a varying number of COLUMNS and ROWS each time the report is generated, so, the columns needed manipulated will be different each time. This is why I need to use Header names.)

1. Select a BLANK cell within the existing report data
2. COPY the cell
3. SELECT all data, except for header, in COLUMN where header name = "PROC#/REV CODE"
4. PASTE SPECIAL ADD (this step is to get two separate sets of data into the same (EQUAL IF) comparable data type.
5. REPEAT this entire process for COLUMN where header name = "FEE RATE".

Thank you SO much for any assistance!

 

[tonyyy]

LittleZenGirl,

You might consider the following...

Sub pasteSpeshul()
Dim blank As Range, found1 As Range, found2 As Range

Set blank = Application.InputBox(prompt:="Please select a blank cell.", Type:=8)
If blank.Count > 1 Or blank.Value <> "" Then
    MsgBox "Please try again."
    Exit Sub
Else
    blank.Copy
    Set found1 = ActiveSheet.Rows(1).Find(What:="PROC#/REV CODE", After:=Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not found1 Is Nothing Then
        Range(found1.Offset(1, 0), Cells(Rows.Count, found1.Column).End(xlUp)).PasteSpecial Paste:=xlPasteAll, operation:=xlAdd
    End If
        
    Set found2 = ActiveSheet.Rows(1).Find(What:="FEE RATE", After:=Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not found2 Is Nothing Then
        Range(found2.Offset(1, 0), Cells(Rows.Count, found2.Column).End(xlUp)).PasteSpecial Paste:=xlPasteAll, operation:=xlAdd
    End If
End If
Application.CutCopyMode = False
End Sub

Cheers,

tonyyy

 

[LittleZenGirl]

Works GREAT, tonyyy! Thanks so much!

 

[tonyyy]

You're very welcome...