Log In
Register
Home
July 29, 2018
Offline
All files are located in a different path
vba code will be placed in a macro.xlsm
i have two files 1.xls & 2.csv
check wheather column H of 1.xls is greater or lower than column D of 1.xls
if column H of 1.xls is greater than column D of 1.xls then match column I of 1.xls with column B of 2.csv & if it matches then put this symbol "<" in column D of 2.csv & copy paste the data of column K of 1.xls in column E of 2.csv
or
if column H of 1.xls is lower than column D of 1.xls then match column I of 1.xls with column B of 2.csv & if it matches then put this symbol ">" in column D of 2.csv & copy paste the data of column K of 1.xls in column E of 2.csv
save and close both the file
plz help me in solving this problem by vba
October 5, 2010
Offline
Hi Sholtan,
This sounds like homework. Have you tried to solve it yourself? Please attach your attempt and I'll be happy to guide you.
Regards
Phil
July 29, 2018
Offline
Philip Sir i dont know much about vba
but i know how to open the file by vba actually i am unable to write the condition in vba language so can u plz help me out here this was very tricky for me so i directly asked for help
July 29, 2018
Offline
Sub test() Dim wb1 As Workbook, wb2 As Workbook Dim ws1 As Worksheet, ws2 As Worksheet Dim rg1 As Range, i As Long, c As Range Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") Set wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv") Set ws1 = wb1.Worksheets.Item(1) Set ws2 = wb1.Worksheets.Item(1) Set rg1 = ws1.Cells(1, 1).CurrentRegion With rg1 For i = 2 To rg1.Rows.Count If .Cells(i, 8) > .Cells(i, 4) Then c = ws2.Columns(2).Find(.Cells(i, 9)) If Not c Is Nothing Then c.Offset(, 2).Value = "<" c.Offset(, 3).Value = .Cells(i, 11) End If Else c = ws2.Columns(2).Find(.Cells(i, 9)) If Not c Is Nothing Then c.Offset(, 2).Value = ">" c.Offset(, 3).Value = .Cells(i, 11) End If End If Next i End With wkb1.Close SaveChanges:=True wkb2.Close SaveChanges:=True End Sub
July 29, 2018
Offline
i tried the code but i met with the error with this line c = ws2.Columns(2).Find(.Cells(i, 9))
October 5, 2010
Offline
Hi,
Please attach all the files you are working with so I don't have to recreate them.
Thanks
Phil
July 29, 2018
Offline
plz see the below attachment
July 29, 2018
Offline
- Sub Not_tested()
- Dim wkb1 As Workbook, wkb2 As Workbook
- Dim sBk1 As String, sBk2 As String
- Dim ws1 As Worksheet, ws2 As Worksheet
- Dim rg1 As Range, i As Long, c As Range
- sBk1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
- sBk2 = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
- Set wkb1 = Workbooks.Open(sBk1)
- Set wkb2 = Workbooks.Open(sBk2)
- Set ws1 = wkb1.Worksheets("1-Sheet1")
- Set ws2 = wkb2.Worksheets("Alert")
- Set rg1 = ws1.Cells(1, 1).CurrentRegion
- With rg1
- For i = 2 To rg1.Rows.Count
- If .Cells(i, 8) > .Cells(i, 4) Then
- Set c = ws2.Columns(2).Find(.Cells(i, 9))
- If Not c Is Nothing Then 'if match found
- c.Offset(, 2).Value = "<"
- c.Offset(, 3).Value = .Cells(i, 11)
- End If
- Else
- Set c = ws2.Columns(2).Find(.Cells(i, 9))
- If Not c Is Nothing Then 'if match found
- c.Offset(, 2).Value = ">"
- c.Offset(, 3).Value = .Cells(i, 11)
- End If
- End If
- Next i
- End With
- End Sub
1 Guest(s)
