Macro to convert abbreviations to its expansions?
 

Hi Guys,

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

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

Any help would be appreciated.

Regards,

Raja


--
Raja
------------------------------------------------------------------------
Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115688

Macro to convert abbreviations to its expansions?
 

Raja, can you supply a sample workbook so we can see your structure and
better understand what you mean?
Raja;415510 Wrote:
Hi Guys,

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

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

Any help would be appreciated.

Regards,

Raja


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115688

Macro to convert abbreviations to its expansions?
 

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: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







"Raja" wrote in message
...

Hi Guys,

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

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

Any help would be appreciated.

Regards,

Raja


--
Raja
------------------------------------------------------------------------
Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=115688