nikhilrock000
New Member
- Joined
- Aug 17, 2017
- Messages
- 2
Hello Guys I have a complicated Questing which I am not able to solve Please Help Me out:
=IF(VLOOKUP(AO4,Matrix!$A:$AG,MATCH(AP4,Matrix!$A$3:$AG$3,0),0)="X","match","To check")
I need above code in VBA and I am sharing all my VAB codes below
Sub Execute1()
Dim ws_zfi As Worksheet
Dim ws_igb As Worksheet
Dim ws_fbl5n As Worksheet
Dim c As Excel.Range
Dim ws_abac As Worksheet
Dim ws_mat As Worksheet
Dim lastrow_d As Long
Dim lr As Long
Dim multiplyer As Integer
multiplyer = 1
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
Set ws_zfi = ActiveWorkbook.Sheets("ZFIGLABACUS")
Set ws_igb = ActiveWorkbook.Sheets("IGB,FAT,noCIT")
Set ws_fbl5n = ActiveWorkbook.Sheets("FBL5N")
Set ws_abac = ActiveWorkbook.Sheets("ABACUS-PG VALIDATION")
Set ws_mat = ActiveWorkbook.Sheets("Matrix")
lastrow_d = ws_zfi.Cells(Rows.Count, 2).End(xlUp).Row
LastCol_d = ws_igb.Cells(2, Columns.Count).End(xlToLeft).Column
For Each c In Range("BG3:BG" & lastrow_d)
If IsNumeric(c) And Not c = "" Then
c.Value = c.Value * 1
End If
Next
On Error Resume Next
For i = 3 To lastrow_d
ws_zfi.Range("AS3:AS" & lastrow_d) = Application.WorksheetFunction.VLookup(ws_zfi.Range("BG3:BG" & lastrow_d), ws_igb.Range("B3:C19"), 2, 0)
Next i
ws_zfi.Range("K3:K" & lastrow_d).Copy
ws_zfi.Range("AX3:AX" & lastrow_d).PasteSpecial
ws_zfi.Range("AX3:AX" & lastrow_d).NumberFormat = "##,##0.00_-;(##,##0.00);-_;"
On Error Resume Next
For i = 3 To lastrow_d
ws_zfi.Range("AY3:AY") = "invoice" & Application.WorksheetFunction.VLookup(ws_zfi.Range("H3:H" & lastrow_d), ws_fbl5n.Range("D1:G" & lastrow_d), 4, 0)
Next i
ws_zfi.Range("AH3:AH" & lastrow_d).Copy
ws_zfi.Range("AZ3:AZ" & lastrow_d).PasteSpecial
ws_zfi.Range("AE3:AE" & lastrow_d).Copy
ws_zfi.Range("BD3:BD" & lastrow_d).PasteSpecial
ws_zfi.Range("O3:O" & lastrow_d).Copy
ws_zfi.Range("BF3:BF" & lastrow_d).PasteSpecial
ws_dump.Range("A2").Select
Selection.AutoFilter Field:=45, Criteria1:=Array( _
"IGN705"), Operator:=xlFilterValues
For Each c In Range("BF3:BF" & Range("BF" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
If c.Value < 0 And c.Offset(, -1).Value <> 0 Then
c.Value = c.Value * -1
End If
Next c
Columns("A").AutoFilter
On Error Resume Next
For i = 3 To lastrow_d
ws_zfi.Range("BH3:BH" & lastrow_d) = Application.WorksheetFunction.VLookup(ws_zfi.Range("BG3:BG" & lastrow_d), ws_igb.Range("B3:D19"), 3, 0)
Next i
ws_zfi.Range("Q3:Q" & lastrow_d).Copy
ws_zfi.Range("BI3:BI" & lastrow_d).PasteSpecial
ws_zfi.Range("R3:R" & lastrow_d).Copy
ws_zfi.Range("BJ3:BJ" & lastrow_d).PasteSpecial
ws_zfi.Range("S3:S" & lastrow_d).Copy
ws_zfi.Range("BK3:BK" & lastrow_d).PasteSpecial
This code i tried but it is not executing I am getting blank
ws_zfi.Range("BL3:BL") = Application.WorksheetFunction.Index(ws_zfi.Range("AO3:AO"), ws_mat.Range("A:AG"), Application.WorksheetFunction.Match(ws_zfi.Range("AP3:AP"), ws_mat.Range("A3:AG3"), 0)
End Sub
=IF(VLOOKUP(AO4,Matrix!$A:$AG,MATCH(AP4,Matrix!$A$3:$AG$3,0),0)="X","match","To check")
I need above code in VBA and I am sharing all my VAB codes below
Sub Execute1()
Dim ws_zfi As Worksheet
Dim ws_igb As Worksheet
Dim ws_fbl5n As Worksheet
Dim c As Excel.Range
Dim ws_abac As Worksheet
Dim ws_mat As Worksheet
Dim lastrow_d As Long
Dim lr As Long
Dim multiplyer As Integer
multiplyer = 1
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
Set ws_zfi = ActiveWorkbook.Sheets("ZFIGLABACUS")
Set ws_igb = ActiveWorkbook.Sheets("IGB,FAT,noCIT")
Set ws_fbl5n = ActiveWorkbook.Sheets("FBL5N")
Set ws_abac = ActiveWorkbook.Sheets("ABACUS-PG VALIDATION")
Set ws_mat = ActiveWorkbook.Sheets("Matrix")
lastrow_d = ws_zfi.Cells(Rows.Count, 2).End(xlUp).Row
LastCol_d = ws_igb.Cells(2, Columns.Count).End(xlToLeft).Column
For Each c In Range("BG3:BG" & lastrow_d)
If IsNumeric(c) And Not c = "" Then
c.Value = c.Value * 1
End If
Next
On Error Resume Next
For i = 3 To lastrow_d
ws_zfi.Range("AS3:AS" & lastrow_d) = Application.WorksheetFunction.VLookup(ws_zfi.Range("BG3:BG" & lastrow_d), ws_igb.Range("B3:C19"), 2, 0)
Next i
ws_zfi.Range("K3:K" & lastrow_d).Copy
ws_zfi.Range("AX3:AX" & lastrow_d).PasteSpecial
ws_zfi.Range("AX3:AX" & lastrow_d).NumberFormat = "##,##0.00_-;(##,##0.00);-_;"
On Error Resume Next
For i = 3 To lastrow_d
ws_zfi.Range("AY3:AY") = "invoice" & Application.WorksheetFunction.VLookup(ws_zfi.Range("H3:H" & lastrow_d), ws_fbl5n.Range("D1:G" & lastrow_d), 4, 0)
Next i
ws_zfi.Range("AH3:AH" & lastrow_d).Copy
ws_zfi.Range("AZ3:AZ" & lastrow_d).PasteSpecial
ws_zfi.Range("AE3:AE" & lastrow_d).Copy
ws_zfi.Range("BD3:BD" & lastrow_d).PasteSpecial
ws_zfi.Range("O3:O" & lastrow_d).Copy
ws_zfi.Range("BF3:BF" & lastrow_d).PasteSpecial
ws_dump.Range("A2").Select
Selection.AutoFilter Field:=45, Criteria1:=Array( _
"IGN705"), Operator:=xlFilterValues
For Each c In Range("BF3:BF" & Range("BF" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
If c.Value < 0 And c.Offset(, -1).Value <> 0 Then
c.Value = c.Value * -1
End If
Next c
Columns("A").AutoFilter
On Error Resume Next
For i = 3 To lastrow_d
ws_zfi.Range("BH3:BH" & lastrow_d) = Application.WorksheetFunction.VLookup(ws_zfi.Range("BG3:BG" & lastrow_d), ws_igb.Range("B3:D19"), 3, 0)
Next i
ws_zfi.Range("Q3:Q" & lastrow_d).Copy
ws_zfi.Range("BI3:BI" & lastrow_d).PasteSpecial
ws_zfi.Range("R3:R" & lastrow_d).Copy
ws_zfi.Range("BJ3:BJ" & lastrow_d).PasteSpecial
ws_zfi.Range("S3:S" & lastrow_d).Copy
ws_zfi.Range("BK3:BK" & lastrow_d).PasteSpecial
This code i tried but it is not executing I am getting blank
ws_zfi.Range("BL3:BL") = Application.WorksheetFunction.Index(ws_zfi.Range("AO3:AO"), ws_mat.Range("A:AG"), Application.WorksheetFunction.Match(ws_zfi.Range("AP3:AP"), ws_mat.Range("A3:AG3"), 0)
End Sub