0

I have the following code:

Dim prevSht As Worksheet
Dim foundCell As Range
Dim foundCol As Long
Dim findStr As String
Dim findRng As Range

Set prevSht = Worksheets("Previous_OnPrem")
findStr = "Date"
Set findRng = prevSht.Range("A:J")

Set foundCell = findRng.Find(What:=findStr, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

ActiveCell.Value = "=IFERROR(VLOOKUP(" & Range("G2").Address(0, 0) & ",Previous_OnPrem!A:J, " & foundCell.Column & "),"""")"
ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":" & "A" & RowCount)

I would like the blank cells to return as Blank

When VLOOKUP doesn't find a matching value, it works properly, but when it does find a match, the blank cells associated returns 0.

I found this similar example as solution to fix it, when it's a simple VLookup

=IF(VLOOKUP(E3,$B$2:$C$7,2,FALSE))=0,"",VLOOKUP(E3,$B$2:$C$7,2,FALSE)

I've tried to adapt it with my actual code in many different ways, but couldn't get it to work.

Kairu
  • 381
  • 1
  • 9
JohnnyHG
  • 11
  • 2
  • `..." & foundCell.Column & ")&"""","""")"` – Scott Craner Mar 20 '23 at 22:15
  • Super, it's Working Thanks a lot. But for some reason, it's now messing up with my Date formats. Instead of returning date values (YYY-MM-DD) on Cells requiring dates, it returns a numeral value (as 4500), even if I enforce a date format, manually or with command : Selection.NumberFormat = "m/d/yyyy" is won't change. Any Idea to fix that ? – JohnnyHG Mar 20 '23 at 23:40
  • If I understand you correctly, maybe you want to try this .... `frm = "VLOOKUP(G2,Previous_OnPrem!A:J," & foundCell.Column & ",FALSE)"` .... `If ActiveCell.Column = 1 Then` ... `With ActiveCell` ... `.Value = "=if(isna(" & frm & "),"""",if(" & frm & "=0,""""," & frm & "))"` ... `.AutoFill Destination:=Range(.Address & ":A" & RowCount), Type:=xlFillDefault` ... `End With` ... `End If`. if the frm result is not available, then it give blank value, if available then check if the frm result is zero then it give blank value, else it give the result. Active cell is assumed will always in column 1 – karma Mar 21 '23 at 03:06
  • Those zeros appear due to calculations. Deactivate that options (you can do it through vba). Check [this](https://support.microsoft.com/en-us/office/display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03) and [this](https://learn.microsoft.com/en-us/office/vba/api/excel.window.displayzeros) – Foxfire And Burns And Burns Mar 21 '23 at 10:15

0 Answers0