VBA to copy specific cells

[topi1]

Hi, can someone please help with the macro where it copies rows (cells) B and C when the value in the corresponding rows(cells) in the column A is 1 or 2. Following is the example. Thank you.

Before VBA
1 a q
1
2 c e
2 d r
3 e t
3 f y

After VBA
a q

c e
d r

 

[Coyotex3]

Does this work? This code uses a formula on column D to extract the data and displays the results in columns D:E.

Sub DataSplit()
Dim i As Long
 Dim LR As Long
 
 LR = Range("A" & Rows.Count).End(xlUp).Row
 
 For i = 1 To LR
    Range("D1:D" & LR).Formula2R1C1 = _
        "=IF(AND(RC[-3]<3,RC[-2]:RC[-1]<>""""),RC[-2]:RC[-1],"""")"
 Next

Range("D1:E" & LR).Value = Range("D1:E" & LR).Value
End Sub

This code uses a formula on column D to extract the data and displays the results in columns D:E.

 

[topi1]

Thank you for your prompt response. It gives an error "Object doesn't support this property or method".

 

[Coyotex3]

That's odd, I can't replicate the specific error on my end

Before running the code:

PERSONAL.xlsm
	A	B	C	D	E
1	1	d	e
2	1
3	2	r	d
4	2	d	c
5	3	f	d
6	3	c	c
7	3	g	c
8
9
Sheet5

After running the code:

PERSONAL.xlsm
	A	B	C	D	E	F
1	1	d	e	d	e
2	1
3	2	r	d	r	d
4	2	d	c	d	c
5	3	f	d
6	3	c	c
7	3	g	c
8
Sheet5

 

[Alex Blakenburg]

Please update your profile to show what version of Excel you are using.
@Coyotex3's code will only work if you have MS365, is that the case ?

Also have you put the code into a standard module (not in a sheet module) ?

 

[Coyotex3]

Please update your profile to show what version of Excel you are using.
@Coyotex3's code will only work if you have MS365, is that the case ?

Also have you put the code into a standard module (not in a sheet module) ?

Hi Alex, I actually did not know this code would only work in M365.

Why is that? Genuinely curious as to why this is.

 

[Alex Blakenburg]

Hi Alex, I actually did not know this code would only work in M365.

Why is that? Genuinely curious as to why this is.

I can't conclusive prove it since I only have MS365 but if you stop the code before you replace the formulas with values and look in the spreadsheet you will find that it "spills". Your excel needs to support dynamic arrays for that to work and that needs MS365 or Excel 2021.

 

[Coyotex3]

I can't conclusive prove it since I only have MS365 but if you stop the code before you replace the formulas with values and look in the spreadsheet you will find that it "spills". Your excel needs to support dynamic arrays for that to work and that needs MS365 or Excel 2021.

This makes sense! Thank you. I did see the “spill” message on my end as well. Once OP responds I will try to adjust the code if still needed.

 

[topi1]

Hi Alex, I actually did not know this code would only work in M365.

Why is that? Genuinely curious as to why th2010 version.

Please update your profile to show what version of Excel you are using.
@Coyotex3's code will only work if you have MS365, is that the case ?

Also have you put the code into a standard module (not in a sheet module) ?

2010 version.
I updated my profile as requested.
The code failed in standard and sheet module.
Thank you.

 

[Coyotex3]

2010 version.
I updated my profile as requested.
The code failed in standard and sheet module.
Thank you.

Thanks for letting us know.


Does this one work getting what you need?
Sub DataSplit()
Dim i As Long
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR
Range("D1:D" & i).FormulaR1C1 = _
"=IF(AND(RC[-3]<3,RC[-2]<>"""",RC[-1]<>""""),RC[-2],"""")"
Range("E1:E" & i).FormulaR1C1 = _
"=IF(AND(RC[-4]<3,RC[-3]<>"""",RC[-2]<>""""),RC[-2],"""")"
Next
For i = 1 To LR
Range("D1:E" & i).Value = Range("D1:E" & i).Value
Next
End Sub