macro will be placed in a separate file macro.xlsm
only macro.xlsm file is opened so we have to open rest 2 file by vba
sample1.xlsx is located at C:\Users\WolfieeeStyle\Desktop
sample2.xlsx is located at C:\Users\WolfieeeStyle\Desktop\Upstox
macro.xlsm is located at C:\Users\WolfieeeStyle\Desktop
So … in macro.xlsm means Thisworkbook.Path is C:\Users\WolfieeeStyle\Desktop
So … Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample1.xlsx") is Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
and Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\sample2.xlsx") is Workbooks.Open(ThisWorkbook.Path & "\Upstox\sample2.xlsx")
sheet name can be anything
So , for first worksheet, use item number, Worksheets.Item(1)
( second worksheet is Worksheets.Item(2)
Before:
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
2 |
NSE |
ACC |
EQ |
1014 |
1030 |
955.5 |
998.45 |
957.4 |
|
3 |
NSE |
ADANIPORTS |
EQ |
27.35 |
27.75 |
25.65 |
25.65 |
25.85 |
|
4 |
|
|
|
|
|
|
|
|
|
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
2 |
NSE |
ACC |
EQ |
1014 |
1030 |
955.5 |
998.45 |
957.4 |
|
3 |
NSE |
ADANIPORTS |
EQ |
27.35 |
28 |
29 |
30 |
27.35 |
|
4 |
|
|
|
|
|
|
|
|
|
Run macro:
Code:
' ThisWorkbook is macro.xlsm is locatred at C:\Users\WolfieeeStyle\Desktop
' so ThisWorkbook.path = "C:\Users\WolfieeeStyle\Desktop"
Sub conditionally_replaceentirerow() ' http://www.excelfox.com/forum/showthread.php/2438-replace-the-entire-row
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Set Wb1 = Workbooks("sample1.xlsx")
'Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample1.xlsx")
'Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
Set Ws1 = Wb1.Worksheets.Item(1) ' worksheet of first tab
Set Wb2 = Workbooks("sample2.xlsx") '
'Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\sample2.xlsx")
'Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\Upstox\sample2.xlsx")
Set Ws2 = Wb2.Worksheets.Item(1) ' worksheet of first tab
'1b Ranges
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Ws1.Range("A1").CurrentRegion
Set Rng2 = Ws2.Range("A1").CurrentRegion
Rem 2 Delete an entire row in Ws2 if value in column B is not anywhere in column A of Ws1
Dim Rws As Long
For Rws = 2 To Rng2.Rows.Count
If Rng2.Range("H" & Rws & "").Value = Rng2.Range("D" & Rws & "").Value Then ' If column H of sample2.xlsx matches with Column D then
Dim rngFnd As Range
Set rngFnd = Rng1.Range("B2:B" & Rng2.Rows.Count).Find(what:=Rng2.Range("B" & Rws & "").Value, After:=Rng1.Range("B2"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) ' https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49181464#49181464
If rngFnd Is Nothing Then ' The value from column B in Ws2 was not found in column B of Ws1
' do nothing
Else
rngFnd.Offset(0, -1).Resize(1, Rng2.Columns.Count).Copy ' B offest -1 is A resize to all columns in range B
Rng2.Range("A" & Rws & "").PasteSpecial Paste:=xlPasteAllUsingSourceTheme '
End If
Else
' column H of sample2.xlsx DOES NOT matches with Column D do nothing
End If
Next Rws
' Wb1.Save
' Wb1.Close
' Wb2.Save
' Wb2.Close
End Sub
After Results
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
2 |
NSE |
ACC |
EQ |
1014 |
1030 |
955.5 |
998.45 |
957.4 |
|
3 |
NSE |
ADANIPORTS |
EQ |
27.35 |
27.75 |
25.65 |
25.65 |
25.85 |
|
4 |
|
|
|
|
|
|
|
|
|
sample2.xlsx : https://app.box.com/s/xavyjz9q6ek3qknam42yif4f7l0qxdfa
Reply With Quote
Originally Posted by fixer
Bookmarks