Assistance with adding if then logic to working VBA script.

evenyougreg

New Member
Joined
Oct 1, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I am looking to add some basic logic to my existing VBA script that copies over cell contents from one spreadsheet to another if a condition is met. I didn't come up with the script, I had a lot of help, so I'm not sure how to incorporate the new logic.

The script as it stands right now does this:

If spreadsheet "OLD.xlsx" finds a string match in spreadsheet "NEW.xlsx" in column A, then the contents of columns E, H, I, J and K are copied over from OLD to NEW, row for row all the way until the end.

VBA Code:
Sub Copy()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Workbooks("OLD.xlsx").Sheets("list")
Set sh2 = Workbooks("NEW.xlsx").Sheets("list")
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
                c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)
            End If
        Set fn = Nothing
    Next
End Sub

Works super well, except I have a new requirement as I mentioned. And that is, if column B does not have the text "NOT FOUND" in it, then I only need the following chunk of code ran which accounts for H I J K, leaving out E.

VBA Code:
                c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)

And if it does, than the original 2 lines are to be ran, which are E H I J K.

VBA Code:
                c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
                c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)

Hopefully that makes sense, and thanks in advance!

Greg
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows
Which sheet do you need to check for Not Found?
Also you code is copying col E & F, along with H:K, should col F still be copied?
 

evenyougreg

New Member
Joined
Oct 1, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Oh interesting, I had no idea F was being copied! No, F should not be copied, thanks for catching that. To answer your question, if the "NEW" spreadsheet has "NOT FOUND" anywhere in column B.

Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Copy()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Workbooks("OLD.xlsx").Sheets("list")
Set sh2 = Workbooks("NEW.xlsx").Sheets("list")
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If fn.Offset(, 1) = "NOT FOUND" Then c.Offset(, 4).Copy fn.Offset(, 4)
                c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)
            End If
        Set fn = Nothing
    Next
End Sub
 
Solution

evenyougreg

New Member
Joined
Oct 1, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
That's exactly what I needed. :biggrin:

Thank. you. so. much.

Greg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,831
Messages
5,627,149
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top