Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
State names into abbreviations | Excel Worksheet Functions | |||
How do I get key codes for chemical Abbreviations | Excel Discussion (Misc queries) | |||
Validation for state abbreviations | Excel Discussion (Misc queries) | |||
Convert State abbreviations color coded cell | Excel Worksheet Functions | |||
state abbreviations | New Users to Excel |