Can VBA vlookup keep cell formatting?

[NewAtVba]

Hi, i posted this thread yesterday http://www.mrexcel.com/forum/showthread.php?t=554056 on making my filename dynamic. The solution worked great. I'm now looking to improve on the code and keep the cell formatting of the 4 columns that are being looked up.

This is the current code.


Sub Macro2()
'
' Macro2 Macro
'

'
Application.ScreenUpdating = False
Dim Bottom As Long
Bottom = Range("A65536").End(xlUp).Row

Range("r2").Select
ActiveCell.Formula = "=VLOOKUP($A2,'[" & Range("X3").Value & "]Sheet1'!$A$1:$T$" & Bottom & ",17,0)"
Selection.AutoFill Destination:=Range("r2:r" & Bottom)

Range("s2").Select
ActiveCell.Formula = "=VLOOKUP($A2,'[" & Range("X3").Value & "]Sheet1'!$A$1:$t$" & Bottom & ",18,0)"
Selection.AutoFill Destination:=Range("s2:s" & Bottom)

Range("t2").Select
ActiveCell.Formula = "=VLOOKUP($A2,'[" & Range("X3").Value & "]Sheet1'!$A$1:$t$" & Bottom & ",19,0)"
Selection.AutoFill Destination:=Range("t2:t" & Bottom)

Range("u2").Select
ActiveCell.Formula = "=VLOOKUP($A2,'[" & Range("X3").Value & "]Sheet1'!$A$1:$t$" & Bottom & ",20,0)"
Selection.AutoFill Destination:=Range("u2:u" & Bottom)

End Sub

Cell X3 contains the filename "Master Copy.xlsx".
Any solutions will be greatly appreciated.

 

[Jerry Sullivan]

I'm now looking to improve on the code and keep the cell formatting of the 4 columns that are being looked up.

Hi, If you are referring to the 4 columns in your Active workbook which are possibly having their formats changed by the AutoFill command, here are two approaches:

This is similar to the AutoFill approach but it will just paste formulas and preserve formatting:

With Range("r2")
    .Formula = "=VLOOKUP($A2,'[" & Range("X3").value & "]Sheet1'!$A$1:$T$" & Bottom & ",17,0)"
    .Copy
    .Resize(Bottom - .row + 1, 1).PasteSpecial (xlPasteFormulas)
End With

This enters a formula directly into each cell in your range using R1C1 notation.

With Range("r2:r" & Bottom)
    .FormulaR1C1 = "=VLOOKUP(RC1,'[" & Range("X3").value & "]Sheet1'!R1C1:R" & Bottom & "C20,17,0)"
End With

Just let me know if I misunderstood.

If you were referring to copying over the formatting from each cell that Vlookup finds a match....that would be quite a bit more complex.

 

[NewAtVba]

Hey JS411, thanks for replying.

I am looking to copy over the formatting from each cell that Vlookup finds a match indeed.

How complex are we talking about here?

 

[Jerry Sullivan]

More complex is relative to my first interpretation of your question.
It shouldn't be too hard to do.

It's kind of unusual to want the formatting from your raw data source instead of doing your formatting in your final report. Can you explain why your doing that since it might shed some light on something that would affect how this is done?

A couple of details...
1. Will the formatting the same for all cells in each column, or could there be a mix of formats in the same column?

2. Since the code is "copying" all the formatting from the looked up cell, should it also just copy the value? Is there any reason to have a Vlookup formula since presumably if your source changes, you should re-run the macro to copy the latest formats.

 

[NewAtVba]

Here's a picture of the raw source data.it's referred to as the master copy.

<img src="http://i.imgur.com/RUMwM.jpg" alt="" title="Hosted by imgur.com" />

everyday a new file will be received with data from column A to P. Opening the new file, i want to vlookup this master copy and bring the last 4 columns(Q to T) over to the new file. This new file will now be the master copy.

1. So far the formatting is done manually. Only column S has formatting. it is just filled with a color depending on the status.

2. There is a need for vlookup as the new file will sometimes contain less IDs and it should not be brought over from the old copy.

 

[Jerry Sullivan]

Thanks that helps. I'll be glad to assist with some code.

So far the formatting is done manually. Only column S has formatting. it is just filled with a color depending on the status.

So if all the cells a column are the same format, we should be able to copy the format to all the cells in each column range in the new file. Is that correct (this isn't any easier to code- just faster to run).

There is a need for vlookup as the new file will sometimes contain less IDs and it should not be brought over from the old copy.

Ok we can do that, but another option would be to enter an #N/A or blank if the lookup value isn't in the master. If you aren't going to use the formula to update changes from your master, then the formula wouldn't provide a benefit.

 

[NewAtVba]

So if all the cells a column are the same format, we should be able to copy the format to all the cells in each column range in the new file. Is that correct (this isn't any easier to code- just faster to run).

willl copying the format in each column range tally with the ID? The position of the rows are different between the master and new file(sometimes the new file will have new rows inserted in the middle)

Ok we can do that, but another option would be to enter an #N/A or blank if the lookup value isn't in the master. If you aren't going to use the formula to update changes from your master, then the formula wouldn't provide a benefit.

showing the N/A will be fine. i think there's a little misinterpretation. the main thing i am trying to do is simply automate(as much as possible) bringing over values from the master file to the new file that is sent in daily. that's the benefit i am looking for

 

[Jerry Sullivan]

willl copying the format in each column range tally with the ID?

Yes. It is still using Vlookup. It just adds copy-pastespecial formats.

Try...

Sub Vlookup_Copy_Formats()    
    Dim lngBottom As Long, lngColumn As Long
    Dim strMasterFile As String, strMasterRef As String
    Application.ScreenUpdating = False
 
    With ActiveSheet
        strMasterFile = .Range("X3").value
        strMasterRef = "'[" & strMasterFile & "]Sheet1'!"
        lngBottom = .Cells.Range("A" & .Rows.Count).End(xlUp).row
        For lngColumn = 18 To 21 'Step through Col R - Col U 
            With Workbooks(strMasterFile).Sheets("Sheet1")
                'copy formats from Row 3 of Master
                .Cells(3, lngColumn - 1).Copy
            End With
            With .Cells(2, lngColumn).Resize(lngBottom - 1, 1)
                .PasteSpecial (xlPasteFormats)
                .FormulaR1C1 = _
                    "=VLOOKUP(RC1," & strMasterRef & "R1C1:R" _
                    & lngBottom & "C20," & lngColumn - 1 & ",0)"
                .FormulaR1C1 = .value 'delete this line to keep formula
            End With
        Next lngColumn
    End With
End Sub

 

[NewAtVba]

Wow thanks. The values are correct but it does not work fully. Now the entire column T is highlighted in red. even the cells that did not have formatting before are highlighted.

 

[Jerry Sullivan]

The values are correct but it does not work fully. Now the entire column T is highlighted in red. even the cells that did not have formatting before are highlighted.

Hmmm... I thought we covered that above in clarifying whether all the cells in the same column range should have same formatting (which includes highlighting/ fill).

Perhaps you could post an image of your desired result.