VBA - If cell contains specific text then copy

Question

Hello!

I'm in the final stages of finishing my latest project and need some help.

I have a 3rd party file supplied to us which contains a value and a letter, like below (lets call it DATA):

Cost	Terms
£1000	P
£2000	P
£1568	C
£8765	P
£10264	C

The P means Prepaid, and C means Collect.

I need to copy the values into the correct column of another sheet (we'll call this one MASTER). The second sheet (MASTER) contains 2 columns again, but instead of having terms it has PREPAID and COLLECT at the top like so:

PREPAID	COLLECT

The end result that I need should look like this:

PREPAID	COLLECT
£1000
£2000
	£1568
£8765
	£10264

What I guess I'm looking for is a search result where we look for the letter P and if P is found it's copied into the relative field on the MASTER sheet, and if C is found the same thing happens. I need to make sure that the entire column remains in the same list, and all the prepaid ones do not appear at the top of the list and the collect ones do not appear at the bottom. If the spreadsheet worked in that way I could autofilter and then copy accordingly, but sadly this one's a little more difficult.

I've trailed the internet for the past 3 hours trying to find a suitable fix, but have got no where.

Thanks,

Daniel

Answer 1

I managed to do this by inserting the rows in the DATA sheet then inserting the formula and duplicating that formula down the line in order to seperate the two, and then copying them over from there. Worked nicely!

Thanks for the help. It's really appreciated. Allowed me to think from things at another angle!

Answer 2

Hi,

try this..

in MASTER sheet, in cells A2 and B2:

Answer 3

Hello,

While this is helpful, sadly it is not what I'm looking for. Reason being that this is a worksheet generator that I'm working on. You import the resources (in this case, DATA sheet), then click a "apply changes" button which then runs a script to add the relative content to the master sheet, then you generate the editable spreadsheet on the final stage of the generator. Once you apply the changes the imported sheets do not come with the the new spreadsheet that has been generated.

Sadly my staff have a tendency to delete things that are not visible, such as formulas, and locking the cells isn't an option.

I need to achieve the same result but in VBA coding as this is more reliable and shouldn't cause any confusion. However, having read it all again, maybe I could create a column or two and input the formula in the DATA spreadsheet and then copy over accordingly. Is there a way to copy any row caps (so as you see it) along with the data as this could work (I'm not in the office now so cannot test run it)!

The saying we have at work is "If it's not David proof then it's not good enough" meaning that David is very clumsy and will delete anything and everything if he can. He means no harm though!

Thank you for the comment. Maybe you could help with a VBA alternative?

Thanks,

Daniel

Answer 4

Hi,

All formulae are in MASTER sheet  (Not in DATA sheet).

Also, you can protect the cells (in MASTER sheet), in columns A and B.

xxxxxxxxxxxxxxxxxxxxx

here is a vba macro

(make a copy before you run this code)

[EDIT..]

Sub ttest1()

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("DATA") '<< DATA sheet, change name

Set ws2 = Sheets("MASTER") << MASTER sheet, change name

Dim r1 As Long, r2 As Long

r1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row

r2 = ws2.Range("A:B").Find(What:="*", After:=ws2.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

If r2 = 1 Then r2 = 2

If r1 > 1 Then

ws2.Range("A2:B" & r2).ClearContents

For Each rr In ws1.Range("A2:A" & r1)

If rr.Offset(, 1).Value = "P" Then ws2.Cells(rr.Row, rr.Column) = rr.Value

If rr.Offset(, 1).Value = "C" Then ws2.Cells(rr.Row, rr.Column).Offset(, 1).Value = rr.Value

Next

End If

End Sub