Fixed Columns, Variable Rows in Excel

[tubbsy123]

Hi,

I have an excel spreadsheet with 9 fixed columns but a variable number of rows. I have written some code that changes the formating of each cell and this works for the variable number of columns.

I am having problems trying to get the code run for the 9 columns instead of the one where the cursor is placed. My code is:

Sub ChangeFormats()
Variable = InputBox("type number of records", "edit recs")
For i = 1 To Variable
Application.SendKeys ("{F2}")
Application.SendKeys ("{F2}")
Application.SendKeys ("{ENTER}")
Next i
End Sub

Any help would be much appreciated.

Regards

Tubbsy123

 

[ClulessChris]

Sorry but I don't see what you're trying do do with that code?

to amend a number of rows you could name them:

for I = 1 to 4
Range("A" & I) = "Some thing here"
next

or

for I = 1 to 4
cells(i,1) = "Some thing here"
next

or use .offset

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[xlbo]

This looks like a convert text to number routine

try this instead

dim lRow as long

lrow = cells(65536,1).end(xlup).row

Range("J1").value = 1
Range("J1").copy
Range("A1:I" & lRow).pastespecial Paste:=xlPasteValues, Operation:=xlMultiply
application.cutcopymode = false
Range("J1").clearcontents

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

"I have written some code that changes the formating of each cell..."

Just to piggyback on what Geoff posted...

When you FORMAT a range, nothing changes in the underlying values of that range. NOTHING!

So, if you had numeric characters in a range that was formatted as TEXT, and you change the format to some NUMERIC format (General, Number, Currency, Date, Time, etc), the VALUES are still the same. NOTHING CHANGES.

What Geoff's code does is CHANGE the all-numeric-character values from TEXT to NUMEIC, by multiplying the values in that range by 1.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]