Posts by wonka1234

Re: Shift+Ctrl+Enter Into VBA

Quote from Batman;754173

Hi,

Two problems:

1. In order to assign a formula to a range of cells in one statement, you need to use .FormulaR1C1 (with the formula defined in the R1C1 notation format), but this will not create the array formula you need. The alternative is to use .FormulaArray to put the formula into F2 and then .FillDown and .Calculate (depending on whether or not your macro has turned off automatic calculation).

2. Without seeing the data that you are trying to convert it's impossible to understand whether the formula can be changed so that it can be used in your macro. Can you upload a sample workbook?

Display More

Hi I attached a sample workbook,

Re: Shift+Ctrl+Enter Into VBA

Quote from Batman;754140

You will need to find a way of shortening the formula, as there is a limit of 255 characters when creating array formulas in VBA.

ahhh you might be right lol, makes sense now. any ideas on how to shorten to just show alphanumeric numbers/letters?

Re: Shift+Ctrl+Enter Into VBA

Quote from Salim Ahmad;754138

type
Range("F2:F5000").FormulaArray=
"and the rest of the formula"

Tried it, didnt work.

Hi guys,my codes purpose is to shorten fields to make them only alphanumeric in a new column.
The following code does not work as a code because I do not know how to do shift+ctrl+enter in code.
Doing it with just the formula is fine and works fine with dragging down, but I want to use a macro so I can automate this throughout a directory.
I have tried .FormulaArray and get a error.

any help on making this a usable macro would be greatly appreciated. Thanks.

Sub Indirect()


Range("F2:F5000").Formula = "=LEFT(E2, MIN(ROW(INDIRECT(""1:""&LEN(E2)))+(((CODE(MID(UPPER(E2), ROW(INDIRECT(""1:""&LEN(E2))), 1))>64)*(CODE(MID(UPPER(E2), ROW(INDIRECT(""1:""&LEN(E2))), 1))<91))+((CODE(MID(E2, ROW(INDIRECT(""1:""&LEN(E2))), 1))>47)*(CODE(MID(E2,  ROW(INDIRECT(""1:""&LEN(E2))), 1))<58)))*1E+99)-1)"








End Sub