I'm trying to get VBA to select the contents of a cell and stick it int
a variable for me. I need it formatted and so I'm trying the followin
but it doesn't work. Var is returned as "- 1"

Var = Format(Range(Row & Col).Select, "### #### ##")

tried this but it then sets Var to empty
Var = Format(Cells(Row, Col).Value, "### #### ##")


Can anybody help me

--
Message posted from http://www.ExcelForum.com

Maybe try:

Var = Format(Range(Row & Col).Value, "###,###.00")
You may be running into problems w/ the spaces between # signs (not
recognized?). Or, you could also try:
Var = Application.WorksheetFunction.Text(Range(R & C).Value, "###,###.##")

Hope that helps.

Gaston < wrote:
I'm trying to get VBA to select the contents of a cell and stick it into
a variable for me. I need it formatted and so I'm trying the following
but it doesn't work. Var is returned as "- 1"

Var = Format(Range(Row & Col).Select, "### #### ##")

tried this but it then sets Var to empty
Var = Format(Cells(Row, Col).Value, "### #### ##")


Can anybody help me?


---
Message posted from http://www.ExcelForum.com/

An example that I've used in a long past app.

WSht.Cells(lfr, 4) = Format(BkrDet(ro, 1) / 100 * TotalComm, FmtMil)
Where, FmtMil = "#,###,##0.00;(#,###,##.00)"
--
Regards;
Rob
------------------------------------------------------------------------
"Gaston " wrote in message
...
I'm trying to get VBA to select the contents of a cell and stick it
into
a variable for me. I need it formatted and so I'm trying the following
but it doesn't work. Var is returned as "- 1"

Var = Format(Range(Row & Col).Select, "### #### ##")

tried this but it then sets Var to empty
Var = Format(Cells(Row, Col).Value, "### #### ##")


Can anybody help me?


---
Message posted from http://www.ExcelForum.com/

Woah. That was the problem. Wasn't recognizing spaces. Thanks
million.

However, I just have two minor problems now. The string I want t
extract from the cell needs to be the first 11 characters. How do
just chose, say the first 11 characters and discard the rest?

Also, if I want to get the last two characters of a 3 character string
how do I do this as well

--
Message posted from http://www.ExcelForum.com

Whatever = Left$(Whatever,11)
For the right - Guess, that's right ...=Right$(Whatever,2)


--
Regards;
Rob
------------------------------------------------------------------------
"Gaston " wrote in message
...
Woah. That was the problem. Wasn't recognizing spaces. Thanks a
million.

However, I just have two minor problems now. The string I want to
extract from the cell needs to be the first 11 characters. How do I
just chose, say the first 11 characters and discard the rest?

Also, if I want to get the last two characters of a 3 character
string,
how do I do this as well?


---
Message posted from http://www.ExcelForum.com/

Why, oh why do small little issues creep up time and time again? :|

Almost got this thing to work. It's printing numbers from one
spreadsheet to the next. There's yet ANOTHER small issue that's come
up.

When I get the address of the field in the second database(Format:
$C12) I use that to then copy and past another column of the same row
by extracting the 12. However, problems arise, since if rows aren't a
fixed lenght. How can I get it to extract the row when the format
changes(ie. I get addresses $C1, $C12, $C133, etc... variable size)

Thanks YET again...


---
Message posted from http://www.ExcelForum.com/