copying numerous formats from a VLOOKUP
 

I have the following formula -
VLOOKUP($A$16,$D$37:$DV$47,COLUMN(E16)-1,FALSE) allowing me to populate
cells c16:dv16. I've coppied this formula from row 16:36. I would like the
lookup to also copy the formats from the lookup table (d37:dv47). There are
numerous formats along these rows, too many for conditional formatting. I
believe I'll need to do this in VB. But alas, my VB skills are rudimentary
at best.
Help will truly be appreciated!

copying numerous formats from a VLOOKUP
 

update:
The formula now looks like this (cheers Pete_UK!)
=IF(ISNA(VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13)-1,FALSE)),"",VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13 )-1,FALSE))

I also need the VBA to copy the formulas, not just the formats, from the
data table.




"olrustyxlsuser" wrote:

I have the following formula -
VLOOKUP($A$16,$D$37:$DV$47,COLUMN(E16)-1,FALSE) allowing me to populate
cells c16:dv16. I've coppied this formula from row 16:36. I would like the
lookup to also copy the formats from the lookup table (d37:dv47). There are
numerous formats along these rows, too many for conditional formatting. I
believe I'll need to do this in VB. But alas, my VB skills are rudimentary
at best.
Help will truly be appreciated!

copying numerous formats from a VLOOKUP
 

Assuming you wanted to loop through Sheet2!A17:A20, find those values in
Sheet1!D37:D47 and return the formulas and formatting from column O of
Sheet1, you can use the Find method inside of a For/Each loop. Change
worksheet/range references as needed and be sure to practice with it on a
copy of your workbook.

Sub Test()
Const lngOffset As Long = 11
Dim rngSource As Range
Dim rngFound As Range
Dim rngCriteria As Range
Dim rngCell As Range

Set rngCriteria = Worksheets("Sheet2").Range("A17:A20")
Set rngSource = Worksheets("Sheet1").Range("D37:D47")

For Each rngCell In rngCriteria
Set rngFound = rngSource.Cells.Find( _
what:=rngCell.Value, _
after:=rngSource.Range("A1"), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)

If Not rngFound Is Nothing Then
rngFound.Offset(0, lngOffset).Copy
rngCell.Offset(0, 1).PasteSpecial Paste:=xlFormulas
rngCell.Offset(0, 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Set rngFound = Nothing
End If
Next rngCell

End Sub




"olrustyxlsuser" wrote:

update:
The formula now looks like this (cheers Pete_UK!)
=IF(ISNA(VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13)-1,FALSE)),"",VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13 )-1,FALSE))

I also need the VBA to copy the formulas, not just the formats, from the
data table.




"olrustyxlsuser" wrote:

I have the following formula -
VLOOKUP($A$16,$D$37:$DV$47,COLUMN(E16)-1,FALSE) allowing me to populate
cells c16:dv16. I've coppied this formula from row 16:36. I would like the
lookup to also copy the formats from the lookup table (d37:dv47). There are
numerous formats along these rows, too many for conditional formatting. I
believe I'll need to do this in VB. But alas, my VB skills are rudimentary
at best.
Help will truly be appreciated!

copying numerous formats from a VLOOKUP
 

BTW - the found data gets pasted in the column to the right of the cell
containing the value you are looking for.


"olrustyxlsuser" wrote:

update:
The formula now looks like this (cheers Pete_UK!)
=IF(ISNA(VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13)-1,FALSE)),"",VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13 )-1,FALSE))

I also need the VBA to copy the formulas, not just the formats, from the
data table.




"olrustyxlsuser" wrote:

I have the following formula -
VLOOKUP($A$16,$D$37:$DV$47,COLUMN(E16)-1,FALSE) allowing me to populate
cells c16:dv16. I've coppied this formula from row 16:36. I would like the
lookup to also copy the formats from the lookup table (d37:dv47). There are
numerous formats along these rows, too many for conditional formatting. I
believe I'll need to do this in VB. But alas, my VB skills are rudimentary
at best.
Help will truly be appreciated!

copying numerous formats from a VLOOKUP
 

I've copied over the vb and adjusted the ranges. There is only 1 sheet in
this workbook. If I understood the VB correctly, I've set the ranges as
Set rngCriteria = Worksheets("Sheet1").Range("A15:A36")
Set rngSource = Worksheets("Sheet1").Range("D37:D47")
Where the rngSource is the lookup table. The vlookup originates in column a
(A15), and the values paste across the row from cells D15:DV15. This then
continues for rows 16:36.
Pasting the code in has not changed the result of the lookup. Neither
formats nor formulas are copied.
What am I doing wrong?
"JMB" wrote:


BTW - the found data gets pasted in the column to the right of the cell
containing the value you are looking for.


"olrustyxlsuser" wrote:

update:
The formula now looks like this (cheers Pete_UK!)
=IF(ISNA(VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13)-1,FALSE)),"",VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B13 )-1,FALSE))

I also need the VBA to copy the formulas, not just the formats, from the
data table.




"olrustyxlsuser" wrote:

I have the following formula -
VLOOKUP($A$16,$D$37:$DV$47,COLUMN(E16)-1,FALSE) allowing me to populate
cells c16:dv16. I've coppied this formula from row 16:36. I would like the
lookup to also copy the formats from the lookup table (d37:dv47). There are
numerous formats along these rows, too many for conditional formatting. I
believe I'll need to do this in VB. But alas, my VB skills are rudimentary
at best.
Help will truly be appreciated!