Excel macros

calleyw

Right I have a very long spreadsheet 2870 rows long only 5 columns wide. In column A I have a list of numbers. Some have symbol by the side of them.

I need to prefix each number with another number. So what I want it to do, is if the cell has a symbol X I want to add 1234 to the front of it, but if no symbol I want it to add 5678 to the front of it.. Also I only want to do it for the first 800 rows rather than the 2870 rows.

I know it is macro but I am a bit clueless about them. Had a quick google but have been unable to find what I want. Anybody have any ideas or pointers.

Thanks in advance.

Yours

Calley

k1rkyc

Sounds like you need to create an 'IF' sum - I'm no expert though so can't help any further.... try the Microsoft Office Forums maybe?

isofa

You need to convert the value of the cell to a String (i.e.text), unless it is text already.

Obviously first you need to check if the X appears in the cell you are talking about, that is easy:

If cell you require . value="X" then...

If all the numbers in the cells are 4 digits, i.e. 1000 to 9999, then you won't need to mess about with converting to strings, just add 50000 to the value of each cell. Any value from 1000 to 9999 + 50000 = 5xxxx

However if the values vary, it may be easier to convert to string (text) and literally prefix with a 5.

In VBA (the macro language) use CStr() to convert from a value to a string.

Then prefix in code, eg if cellvalue is the value you want to end up with, and originalvalue is the current non-prefixed value:

cellvalue="5" & originalvalue

e.g. providing you've declared some variables, such as strOriginalValue

strOriginalValue = Cells(1,1).Value

if the sheet is numeric not text, convert the original value with Cstr()

i.e. to set A1 toCells(1,1).Value="5" & strOriginalValue

Then loop through each line, until you are at row 800 and then step out.

e.g. go through Cells(1, 1) (A1) to Cells (800,1) (A800), i.e. put your loop counter: Cells(loopcounter,1)

If you wanted to make it easier, insert a column and just set up a similar formula for all the cells, which will achieve the same, but in a separate column. You then don't have to make a loop, just don't add the forumula past the cells you don't want.

PhilCo_2

No need for a macro unless you're wanting to automate the file. If the x is the first character in the cell (assuming a2 is first line) put...

=IF(LEFT(A2,1)="x",1234&A2,5678&A2)

in a blank column and copy the formula down to row 800.

calleyw

PhilCo wrote: »

No need for a macro unless you're wanting to automate the file. If the x is the first character in the cell (assuming a2 is first line) put...

=IF(LEFT(A2,1)="x",1234&A2,5678&A2)

in a blank column and copy the formula down to row 800.

Nope X is the last character in each cell.


Yours


Calley

PhilCo_2

Needs to be RIGHT to bring back last character then.

=IF(RIGHT(A2,1)="x",1234&A2,5678&A2)

calleyw

PhilCo wrote: »

Needs to be RIGHT to bring back last character then.

=IF(RIGHT(A2,1)="x",1234&A2,5678&A2)

I will give a whirl. So it will add 1234 if it has a X on the right handside but if not it will add 5678 to the front of the numbers already in the cell.


Yours


Calley

isofa

calleyw wrote: »

I will give a whirl. So it will add 1234 if it has a X on the right handside but if not it will add 5678 to the front of the numbers already in the cell.


Yours


Calley

Adding isn't the same as prefixing (as per your original post)...

calleyw

isofa wrote: »

Adding isn't the same as prefixing (as per your original post)...

No I don't want to add the numbers together I want to prefix the numbers.

Sorry when I went meant add I was not meaning sum of the two lots of numbers.

But not sure what I am doing can't see to get the "if" statement to work

Yours

Calley

PhilCo_2

Copy the code and if column F is blank on your file paste it in cell F2. The code will look for the last right character in cell A2, if x you'll get 1234 in front if not you'll get 5678. If it's not picking out the x's check to make sure there are no spaces after the x (press F2 on the code to go to cell edit mode).

f1charlie

Right click on the cell containing the formula, select Format Cells>Number>General>OK. Select the cell and click in the formula bar at the top and hit return.
I sometimes find that Excel treats the cell as text rather than a formula and this sorts it out.