Excel: Calling all Microsoft Excel Experts

[stone]

I have a spreadsheet with a column of data with approx 800 rows. I need to add an X as the first character in each of the cells. Any experts advise as to the easiest way of adding this.

[wiggy001]

stone said:

I have a spreadsheet with a column of data with approx 800 rows. I need to add an X as the first character in each of the cells. Any experts advise as to the easiest way of adding this.

If your data is in cells A1 to A800, in cell B1 type:

=CONCATENATE("X",A1)

Copy this down to cell B800 and Robert is your father's brother.

[pdV6]

In Tools->Macro->VB Editor, create the following routine and run it (remembering to change the starting values accordingly)

Macro said:

Sub MacroX() Dim Col As String, RowStart As Integer, RowEnd As Integer, i As Integer Col = "B" 'Change this to your column RowStart = 1 ' Change this to your 1st row RowEnd = 5 ' change this to your last row For i = RowStart To RowEnd Range(Col & CStr(i)).Select ActiveCell.FormulaR1C1 = "X" & ActiveCell.FormulaR1C1 Next i End Sub

[bga]

wiggy001 said:

stone said: I have a spreadsheet with a column of data with approx 800 rows. I need to add an X as the first character in each of the cells. Any experts advise as to the easiest way of adding this. If your data is in cells A1 to A800, in cell B1 type: =CONCATENATE("X",A1) Copy this down to cell B800 and Robert is your father's brother.

Alternatively in B1 you can enter ="X"&A1 which will save having to type a long word Still need to copy to all cells though.

If you want to keep it you can:

1.highlight the column
2.edit -> copy
3.edit -> paste special
4.paste as values

which will remove the formula.

Prefer macro though. Why take 2 mins to do a task when you can spend 15 writing a macro!

[stone]

Thanks!!

[pdV6]

bga said:

Prefer macro though. Why take 2 mins to do a task when you can spend 15 writing a macro!

Took about 30 sec!

[wiggy001]

bga said:

Alternatively in B1 you can enter ="X"&A1 which will save having to type a long word

True... why use a long word when a diminutive one will do?

[Fer]

New request. I have a spreadsheet with several workbooks, built from an extract from Alterian.

Any hints for a macro to go through each workbook, and save it as HTML in a file with the same name as the workbook.

Answers on a postcard to the usual address. TIA.

[pdV6]

Sub SaveHTML()
Dim i As Integer
For Each Sheet In ActiveWorkbook.Sheets
i = i + 1
ActiveWorkbook.PublishObjects.Add(xlSourceSheet, "C:\Temp\" & Sheet.Name & ".htm", Sheet.Name, "", xlHtmlStatic, "Test" & CStr(i), "").Publish (True)
Next
End Sub


>> Edited by pdV6 on Wednesday 4th August 15:43

[Fer]

pdV6 said:

Sub SaveHTML() Dim i As Integer For Each Sheet In ActiveWorkbook.Sheets i = i + 1 ActiveWorkbook.PublishObjects.Add(xlSourceSheet, "C:Temp" & Sheet.Name & ".htm", Sheet.Name, "", xlHtmlStatic, "Test" & CStr(i), "").Publish (True) Next End Sub

Thanks Pete. That's another PH I owe.

[bga]

pdV6 said:

bga said: Prefer macro though. Why take 2 mins to do a task when you can spend 15 writing a macro! Took about 30 sec!

macro man to the rescue

[pdV6]

bga said:

macro man to the rescue

Easiest way to get started on XL Macros is to simply record what you want to do and then go and look to see what it created for you. Simple to then adapt it as necessary.