Macro Help

[Joesif]

Hello,

I am a noob to VBA and macros.

What I am trying to do is copy text into the middle of my formula which is a sumif formula

=SUMIF('[SS.BARCODES_29-07-19_15-47.xlsx]SS.BARCODES_29-07-19_15-47'!$A:$A,"*NEEDDATACOPIEDHERE*",'[SS.BARCODES_29-07-19_15-47.xlsx]SS.BARCODES_29-07-19_15-47'!$B:$B)

My formula is in column D and the text I am copying is on the same line but in column B.

What I have as my macro is

Sub TEST1()
'
' TEST1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Range("B10").Select
ActiveCell.FormulaR1C1 = "NEXTCURT10B"
Range("D10").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('[SS.BARCODES_29-07-19_15-47.xlsx]SS.BARCODES_29-07-19_15-47'!C1,""*NEXTCURT10B*"",'[SS.BARCODES_29-07-19_15-47.xlsx]SS.BARCODES_29-07-19_15-47'!C2)"
Range("D11").Select
End Sub

My issue is that the macro runs the same line each time so I need to move it on to the row below.

Thanks in advance
Joe

 

[mohadin]

Hi
may Be

Sub TEST1()
'
' TEST1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'


Range("B10:B100").Select
ActiveCell.FormulaR1C1 = "NEXTCURT10B"
Range("D10:D100").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('[SS.BARCODES_29-07-19_15-47.xlsx]SS.BARCODES_29-07-19_15-47'!C1,""*NEXTCURT10B*"",'[SS.BARCODES_29-07-19_15-47.xlsx]SS.BARCODES_29-07-19_15-47'!C2)"
Range("D11").Select
End Sub

But If you provide more about the size and data layout it could be neater

 

[mohadin]

Sub TEST1()


' Keyboard Shortcut: Ctrl+Shift+R
Range("B10:B100").FormulaR1C1 = "NEXTCURT10B"
Range("D10:D100").FormulaR1C1 = _
"=SUMIF('[SS.BARCODES_29-07-19_15-47.xlsx]SS.BARCODES_29-07-19_15-47'!C1,""*NEXTCURT10B*"",'[SS.BARCODES_29-07-19_15-47.xlsx]SS.BARCODES_29-07-19_15-47'!C2)"
Range("D11").Select
End Sub

 

[Joesif]

Thanks for your reply.

I am now getting an issue in column B as the macro is copying over the data held here. What I actually need is for each time the data in each row of column b to be copied into the formula on that line in column D

Thanks
JOe

 

[Joesif]

I have attached a screen shot above to show what I am after.

In column D i have my formula SUMIF. I need to copy the data in column B into the middle of the formula between the "**" for each row. So row 10 will have "*NEXTCURT10B*" for the middle part of my sumif formula