nass
06-16-2010, 07:10 AM
hello everyone,
i'm just starting up with VBA as a means to automate some procedures in my work environment.
i have recorded a macro in excel and i would like to generalize its functionality..
the macro is supposed to do abit of formatting and also produce a couple of other columns with computed values from the input ones.
problem is: the number of entries for each sheet that i let the macro run on differs...
so the range in which i need the computations to be carried out varies.
one solution would be to let the range of calculation be the whole column range("D1:D65535")... but i find that this is a waste of resources and time.
instead i would use some function that returns to a variable the last non empty cell of a column (as a string perhaps) and use that variable in place of "D65535"...
how do i do that however?
here is the relevant part of the code
basically, a column consists of elevations as text ie '1983 m'
and i strip the ' m' from the text. Then I propagate the formula to the following cells... but the Range("D1:D765") is not constant in all cases...
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D765")
also , after the macro is run (even in sheets with smaller number of rows that 765) i still have to manually select the formatted columns and 'convert them to number'... from the exclamation mark icon that shows up....
how do i circumvent that?
thank you in advance for your help
nass
i'm just starting up with VBA as a means to automate some procedures in my work environment.
i have recorded a macro in excel and i would like to generalize its functionality..
the macro is supposed to do abit of formatting and also produce a couple of other columns with computed values from the input ones.
problem is: the number of entries for each sheet that i let the macro run on differs...
so the range in which i need the computations to be carried out varies.
one solution would be to let the range of calculation be the whole column range("D1:D65535")... but i find that this is a waste of resources and time.
instead i would use some function that returns to a variable the last non empty cell of a column (as a string perhaps) and use that variable in place of "D65535"...
how do i do that however?
here is the relevant part of the code
basically, a column consists of elevations as text ie '1983 m'
and i strip the ' m' from the text. Then I propagate the formula to the following cells... but the Range("D1:D765") is not constant in all cases...
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D765")
also , after the macro is run (even in sheets with smaller number of rows that 765) i still have to manually select the formatted columns and 'convert them to number'... from the exclamation mark icon that shows up....
how do i circumvent that?
thank you in advance for your help
nass