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!

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!

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!

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!

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!