GazTheHat

OK, i know very little VBA and i've got to modify this macro.

It's reading in a value from a file, then placing it in the worksheet. It does this fine, but when it is put in the sheet, there is a "!" box next to the number, which when clicked says to convert it to a number.

Now i would like to do this automatically. I've searched for keyphrases, but i've got nothing so far.

Here's a snip of the macro:

Dim InvoiceTotal
.
.
.
' Open the totals file and process it
Open "C:\temp\totals8.dat" For Input As #1
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, InvoiceTotal ' Read line into variable.
Loop
Close #1 ' Close file.
.
.
.
' Overall Total Clients
Worksheets("Sheet1").Range("O" & intRow + 2).Activate
ActiveCell.Formula = InvoiceTotal
ActiveCell.Font.Bold = True

ChefDude

clng()
cdbl()
cint()

Dracoro

Alos, as a side note, try to avoid using activate and activecell. It slows down processing, can cause confusion to user (excel screen flashing here there and everywhere) and can be a nightmare to maintain as code grows. Also, if you wanted to use something else (vb, or word etc.) that accesses the excel object library you won't be able to convert the code easily.

use: (although you may need to use cell rather than range, I can't remember which in these cases, but you get the general idea etc....)
Worksheets("Sheet1").Range("O" & intRow + 2).Formula = InvoiceTotal
Worksheets("Sheet1").Range("O" & intRow + 2).Font.Bold = True

Swen6

Not sure if this is what your talking about.

RIGHT CLICK on the cells, choose FORMAT CELLS and then check the CATEGORY on the NUMBER TAB, if this is set to the wrong setting when you imput numbers it changes the values.

Sorry if i've got the wrong end....

GazTheHat

clng, of course.

No wander i didn't find it when searching for "convert".

Thanks, worked a treat!!


Now off to try Dracoro's suggestion, because the sheet is populated with about 1500 lines and a dozen cells. It flashes the **** outta the screen, i can't believe the users haven't had seizures.

That's what you get inheriting code from someone who probably didn't have a clue.

GazTheHat

I tried:

Worksheets("Sheet1").Cell("O" & intRow + 2).Formula = InvoiceTotal

Object does not support that method error came up.

Dracoro

It's been a while. Try Cells (put the s in!) and use the up/down left/right. e.g. Cells(21, 4)

Range works though.
Worksheets("Sheet1").Range("O7").Font.Bold = True
Worksheets("Sheet1").Range("a3").Formula = "=1+4"

Just tested and ok.

GazTheHat

Yep, that range works, just amending the macro now.

There are 8 total columns, that are set to bold, could i just set the row to bold?

Maybe this:
Worksheets("Sheet1").Range("O").Font.Bold = True

GazTheHat

Scrap the above request. That was just being lazy. Googled it and found it.

Something like:

Rows(rowindex).EntrireRow.Font.Bold = True

Dracoro

Or use Range("O:O")

Either which way works.