Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy paste the data if condition matches

0

Hello Everyone, I am looking for a macro plz see the details below

Condition 1) Only If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls then Match Column B of sample1.xls with column B of sample2.csv & if it is there then do nothing & if it is not present then paste the column B data of sample1.xls to column B of sample2.csv 

Condition 2) Only If column K of sample1.xls is lower than Column D of sample1.xls & Column H of sample1.xls is lower than column K of sample1.xls then Match Column B of sample1.xls with column B of sample2.csv & if it is there then do nothing & if it is not present then paste the column B data of sample1.xls to column B of sample2.csv 

Plz Note

All files are located in a different path (So in the macro the path will be hardcoded, Assume any path & I will edit the path manually by myself)

Sheet name can be anything

Macro will be putted in a vba.xlsm

Plz see the file that I have attached below

I have explained the details more deeply now, Any doubts plz ask

Plz download csv file from the link below bcoz i was unable to attach it 

https://drive.google.com/file/d/1Ec2plYd0oZq-v7r6itJYOf8BjOLtY3VL/view?usp=sharing

Answer
Discuss

Discussion

Any doubts?
excel1 (rep: 2) Jun 8, '20 at 11:36 pm
Add to Discussion

Answers

0

Hello excel1, 

>>  updated Aug. 19/20  <<

I wasn't able to view the file using the link you provided.

The following will check to see if the conditions are met then check to see if the name on sheet1 is on sheet2 and if not copy it to the next blank line on sheet2.

Sub Find_And_Copy()

' macro written by WillieD24 Ayg. 19/20

' variables - some possibilities
Dim WsSht1, WsSht2 As Worksheet
Dim Lrow1, Lrow2 As Integer
Dim Rx1 As Integer, Rx2 As Integer
Dim FindWhat, Found As String

Set WsSht1 = Worksheets("Sheet1")
Set WsSht2 = Worksheets("Sheet2")

' Find number of rows with data in Column B on WsSht1 and WsSht2
Lrow1 = WsSht1.Cells(Rows.Count, 2).End(xlUp).Row
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row

Rx1 = 2   ' row number starting value on WsSht1; assuming row 1 is a header row
Rx2 = 2   ' row number starting value on WsSht2; assuming row 1 is a header row

' Condition 1:     (H Rx1) > (D Rx1) and (H Rx1) > (K Rx1)

Line101:
If Rx1 > Lrow1 Then End    ' all rows on WsSht1 have been checked
If WsSht1.Cells(Rx1, 11) > WsSht1.Cells(Rx1, 4) And WsSht1.Cells(Rx1, 8) > WsSht1.Cells(Rx1, 11) Then GoTo Line102 Else GoTo Line103

Line102:   ' condition 1 met - check for WsSht1(B) in WsSht2(B)
FindWhat = WsSht1.Cells(Rx1, 2)
Found = WsSht2.Cells(Rx2, 2)
If FindWhat = Found Then GoTo Line103   ' name found - check next WsSht1(B)
Rx2 = Rx2 + 1
If Rx2 > Lrow2 Then GoTo Line104    ' name not found
GoTo Line101

Line103:   ' condition 1 not met or name exists on WsSht2 - check next row on WsSht1
Rx1 = Rx1 + 1
Rx2 = 2
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
If Rx1 > Lrow1 Then GoTo Line200    ' all rows on WsSht1 have been checked for condition 1
GoTo Line101

Line104:   ' name not found on WsSht2
WsSht2.Range("B" & Rx2) = WsSht1.Range("B" & Rx1)   ' enter nam from WsSht1(B) on WsSht2(B)
Rx1 = Rx1 + 1
Rx2 = 2
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
GoTo Line101   ' look for next WsSht(B) in WsSht2(B)

Line200:

' Condition 2:     (H Rx1) < (D Rx1) and (H Rx1) < (K Rx1)

Rx1 = 2
Rx2 = 2

Line201:
If Rx1 > Lrow1 Then End    ' all rows on WsSht1 have been checked
If WsSht1.Cells(Rx1, 11) < WsSht1.Cells(Rx1, 4) And WsSht1.Cells(Rx1, 8) < WsSht1.Cells(Rx1, 11) Then GoTo Line202 Else GoTo Line203

Line202:   ' condition 2 met - check for WsSht1(B) in WsSht2(B)
FindWhat = WsSht1.Cells(Rx1, 2)
Found = WsSht2.Cells(Rx2, 2)
If FindWhat = Found Then GoTo Line203   ' name found - check next WsSht1(B)
Rx2 = Rx2 + 1
If Rx2 > Lrow2 Then GoTo Line204    ' name not found
GoTo Line201

Line203:   ' condition 2 not met or name exists on WsSht2 - check next row on WsSht1
Rx1 = Rx1 + 1
Rx2 = 2
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
If Rx1 > Lrow1 Then End 'GoTo line999
GoTo Line201

Line204:   ' name not found on WsSht2
WsSht2.Range("B" & Rx2) = WsSht1.Range("B" & Rx1)   ' enter nam from WsSht1(B) on WsSht2(B)
Rx1 = Rx1 + 1
Rx2 = 2
Lrow2 = WsSht2.Cells(Rows.Count, 2).End(xlUp).Row
GoTo Line201   ' look for next WsSht(B) in WsSht2(B)


End Sub

See the attached file to try it out.

Hope this helps

Cheers   ;-}

Hope this helps to get you started.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login