Macro to convert abbreviations to its expansions?

[Raja]

Hi Guys,

Currently i have Abbreviations and its expansions in the Sheet2 a
column A and B respectively and in the sheet1 i have abbreviation
filled in the D column and i have applied Vlookup formula with the i
the colmun F of sheet 1 to display the expansions.however the data o
abbreviations is huge and it will change everyday as per the flow w
receive so it is very difficult for the user to drag the vlookup formul
till last data.

So can any tell how this can be sorted though Macro instead of
Vlookup formula.

Any help would be appreciated.

Regards,

Raj

 

[Simon Lloyd]

Raja, can you supply a sample workbook so we can see your structure an
better understand what you mean

Hi Guys

Currently i have Abbreviations and its expansions in the Sheet2 a
column A and B respectively and in the sheet1 i have abbreviation
filled in the D column and i have applied Vlookup formula with the i
the colmun F of sheet 1 to display the expansions.however the data o
abbreviations is huge and it will change everyday as per the flow w
receive so it is very difficult for the user to drag the vlookup formul
till last data

So can any tell how this can be sorted though Macro instead of
Vlookup formula

Any help would be appreciated

Regards

Raj

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Patrick Molloy]

there's no need to "drag" the formula. simply select the last cell with the
formula, hover the mouse over the black dot on the lower right hand corner
so the cursor becomes a black cross, then double-click to fill down

to replicate the formula in F down to the bottom of D in VBA

Option Explicit
Sub copyFormula()
Dim D_last_row As Long
With Worksheets("Sheet1")
D_last_row = .Range("D" &
..Range("D").Rows.Count).End(xlUp).Row
With .Range(.Range("F" & .Range("F:F").Rows.Count).End(xlUp),
..Range("F" & D_last_row))
.FormulaR1C1 = .Range("A1").FormulaR1C1
End With
End With
End Sub

method: find the last row of D set the F range to the last cell used in F
to the cell in F at the last row of D and then copy the formula from the
first cell.

so if last F is F60 and last D is D2200, then F60:F2200 will be used.
the formula in F60 is then set as the formula for all the cells in F60:F2200