Macro to find and paste not working

[Indranil_Guha]

Dear All,

i am trying to build a macro that will copy a range of cells and then it will find a specific date in a table and add the copied cells with the existing values in the column.
i have created the macro but it is working strangely.
i mean when i am trying it step by step (using F8) it is working as desired but when i am playing the macro, it is not working. the coding is as under.
could any one please help me to identify the fault. any help will be appreciated.

Sub test()
Dim ws As Worksheet
Dim rng As Range
Dim targetDate As Range
Sheets("Sale").Select
Range("APK5:APK30").Select
Selection.Copy

Set ws = ThisWorkbook.Worksheets("Sale")


Set targetDate = ws.Range("APK3")


Set rng = ws.Range("3:3").Find(What:=targetDate, LookAt:=xlWhole, MatchCase:=False)


rng.Offset(2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False

End Sub

 

[PCosta87]

Dear All,

i am trying to build a macro that will copy a range of cells and then it will find a specific date in a table and add the copied cells with the existing values in the column.
i have created the macro but it is working strangely.
i mean when i am trying it step by step (using F8) it is working as desired but when i am playing the macro, it is not working. the coding is as under.
could any one please help me to identify the fault. any help will be appreciated.

Sub test()
Dim ws As Worksheet
Dim rng As Range
Dim targetDate As Range
Sheets("Sale").Select
Range("APK5:APK30").Select
Selection.Copy

Set ws = ThisWorkbook.Worksheets("Sale")


Set targetDate = ws.Range("APK3")


Set rng = ws.Range("3:3").Find(What:=targetDate, LookAt:=xlWhole, MatchCase:=False)


rng.Offset(2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False

End Sub

Hi,

It seems to be working here...

A few questions:
Does it stop on your end?
What is the error message?
What line of code is highlighted when you press debug?

 

[Hui]

Works here in Excel 2010 and 2016
Are you using a Mac ?

Also where abouts are you storing the code ?

 

[vletm]

Indranil_Guha
if MAC then rng stays Nothing and after that ... no work!
This works with MAC

Sub test()
    Application.ScreenUpdating = False
    On Error Resume Next
    With Sheets("Sale")
        targetDate = CLng(.Range("APK3"))
        .Range("APK5:APK30").Copy
        x = WorksheetFunction.Match(targetDate, Range("A3:API3"), 0)
        If Err.Number = 0 Then
            .Cells(5, x).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
        Else
            MsgBox "Unknown Date"
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Indranil_Guha]

Thanks a lot every one.

@ vletm, though i am not using Mac but this code is working fine for me.

thanks a lot for the help.