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!D3747 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("D3747")

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("D3747")
Where the rngSource is the lookup table. The vlookup originates in column a
(A15), and the values paste across the row from cells D15V15. 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!