Solved Excel 2007 vba formating question

April 10, 2012 at 09:31:37
Specs: Windows XP
Hi everyone
I'm close to what I want but I cant get it right
What I got

Dim StrAmt As String
'
'
StrDealerfee = myscreen.getString(19, 29, 4)
'
'
StrDealerfee = WorksheetFunction.Substitute(StrDealerfee, "_", "0") <=== would show 0750
'
'
If ActiveWorkbook.Worksheets("WC").Range("C17").Value = "X" Then
Range("J22").Value = "1"
If ActiveWorkbook.Worksheets("WC").Range("E30").Value = "X" Then
Range("H39").Value = "CREDIT"
If StrDealerfee = ActiveWorkbook.Worksheets("WC").Range("H30").Value Then
Range("C33").Value = "Crediting Dealer $" & ActiveWorkbook.Worksheets("WC").Range("H30").Value & (" for Dealer Fee waived")
Else
End If
If StrDealerfee <> ActiveWorkbook.Worksheets("WC").Range("H30").Value Then
Range("C33").Value = "Crediting Dealer $" & ActiveWorkbook.Worksheets("WC").Range("H30").Value & (" for Dealer Fee adjusted")
Else
End If
Else
End If
If ActiveWorkbook.Worksheets("WC").Range("B30").Value = "X" Then
Range("H39").Value = "DEBIT"
Range("C33").Value = "Debiting Dealer $" & ActiveWorkbook.Worksheets("WC").Range("H30").Value & (" for Dealer Fee (RAV Fee)")
Else
End If
Else
End If
'
'

At the end it shows:
Crediting Dealer $750 for Dealer Fee adjusted

but I wanted it to show
Crediting Dealer $750.00 for Dealer Fee waived

Also... ActiveWorkbook.Worksheets("WC").Range("H30").Value = 750 formated as currency, It shows "$750.00", but in the cell is "750"

Thanks for the help in advance


See More: Excel 2007 vba formating question

Report •


✔ Best Answer
April 12, 2012 at 11:43:47
Either I'm not understanding what you are trying to do or you are not understanding my explanation.

I was concentrating on this line:

Range("C33").Value = "Debiting Dealer $" & _
ActiveWorkbook.Worksheets("WC").Range("H30").Value & _
(" for Dealer Fee (RAV Fee)")

You said "ActiveWorkbook.Worksheets("WC").Range("H30").Value = 750 formated as currency, It shows "$750.00", but in the cell is "750""

I am assuming that you are trying to place whatever is showing in H30 into the text string in C33.

Currently you are adding the $ as text here: "Debiting Dealer $" and then using the value from H30 here:

Range("H30").Value

If you drop the $ in "Debiting Dealer $" and change .Value to .Text i.e.

Range("H30").Text

then you will get exactly what is displayed in H30.

If you see $23,750.22 in the H30, then the code below will give you this:

Debiting Dealer $23,750.22 for Dealer Fee (RAV Fee)

Range("C33").Value = "Debiting Dealer " & _
ActiveWorkbook.Worksheets("WC").Range("H30").Text & _
(" for Dealer Fee (RAV Fee)")

It will include the $ and show the decimal digits (even .00) if that what shows in H30.

Isn't that what you want?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 10, 2012 at 18:01:47
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post code in this forum.

Second, I'm assuming that the overall issue is that when H30 displays $750.12 then C33 looks fine, but when H30 displays $750.00, you don't get the .00 in C33. In other words, the problem occurs when there is a whole dollar amount (Hint: an integer value) in H30.

One easy way to fix that is to simply add the .00 to the text string if H30 contains a whole dollar amount:

'Check for integer value in H30
  If Int(Range("H30")) - Range("H30") = 0 Then
'Add .00 to text string if true
    Range("C33").Value = _
    "Debiting Dealer $" & _
    ActiveWorkbook.Worksheets("WC").Range("H30").Value & _
    (".00 for Dealer Fee (RAV Fee)")
  Else
'Use actual value if H30 is not an integer
    Range("C33").Value = _
    "Debiting Dealer $" & _
    ActiveWorkbook.Worksheets("WC").Range("H30").Value & _
    (" for Dealer Fee (RAV Fee)")
  End If

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 12, 2012 at 07:07:56
Ok this partially solves my problem but now I also have a problem with the comma
Intead of showing 1000.00 I want it to show 1,000.00, or 25356.20 as 25,356.20
I guess Im looking for a quick tutorial on INTEGER numbers. How to convert figures into integer numbers.
Any help with that?

Report •

#3
April 12, 2012 at 08:21:17
You have 2 different issues here.

First, let's get the Integer lesson out of the way, but I'm not sure that you need this for what you are trying to do.

There are a number of functions that will convert a value to an integer:

INT(A1) will return the integer portion of a number
e.g. 23.45 and 23.85 will both will become 23

ROUNDDOWN(A1,0) will round the number down based on the number of decimal digits chosen. Choosing 0 will return just the integer potion.

e.g. 23.45 and 23.85 will both become 23

ROUNDUP(A1,0) will round the number up based on the number of decimal digits chosen. Choosing 0 will return the next highest integer.

e.g. 23.45 and 23.85 will both become 24

There are other, more exotic ways, to return just the integer portion of a number, but those are the basics.

However, now that you've expanded on the original question a bit, perhaps you should try the Text property instead of the Value property, which should return exactly what is displayed in the cell.

Sorry that I didn't think of this earlier.


   Range("C33").Value = _
    "Debiting Dealer " & _
    ActiveWorkbook.Worksheets("WC").Range("H30").Text & _
    (" for Dealer Fee (RAV Fee)")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 12, 2012 at 10:36:29
OK Im not explaining myself correctly.
Here's the deal... Im using a SCREENSCRAPE macro
I need for example values as "0750" or " 750" or "750.00" to show as "750.00"
Also amounts as "23750.22" to show as "23,750.22"
Basically I need to catch the amounts and make them currency without the $ symbol.

The problem is when an amount is $750.00 excel would get it into my message as 750 and not 750.00. Or 23,750.22 as 23750.22.

I need the .00 and the comma to show.

lets say this

StrDealerfee = myscreen.getString(19, 29, 4)

StrDealerfee = WorksheetFunction.Substitute(StrDealerfee, "_", "0")

it would now come up as "0750"
how do I convert it to show "750.00" including the .00 and comma every thounsands???

Thanks


Report •

#5
April 12, 2012 at 11:43:47
✔ Best Answer
Either I'm not understanding what you are trying to do or you are not understanding my explanation.

I was concentrating on this line:

Range("C33").Value = "Debiting Dealer $" & _
ActiveWorkbook.Worksheets("WC").Range("H30").Value & _
(" for Dealer Fee (RAV Fee)")

You said "ActiveWorkbook.Worksheets("WC").Range("H30").Value = 750 formated as currency, It shows "$750.00", but in the cell is "750""

I am assuming that you are trying to place whatever is showing in H30 into the text string in C33.

Currently you are adding the $ as text here: "Debiting Dealer $" and then using the value from H30 here:

Range("H30").Value

If you drop the $ in "Debiting Dealer $" and change .Value to .Text i.e.

Range("H30").Text

then you will get exactly what is displayed in H30.

If you see $23,750.22 in the H30, then the code below will give you this:

Debiting Dealer $23,750.22 for Dealer Fee (RAV Fee)

Range("C33").Value = "Debiting Dealer " & _
ActiveWorkbook.Worksheets("WC").Range("H30").Text & _
(" for Dealer Fee (RAV Fee)")

It will include the $ and show the decimal digits (even .00) if that what shows in H30.

Isn't that what you want?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 12, 2012 at 12:43:29
OK that is EXACTLY what I was looking for... I didnt see post# 3 before

THANK YOU SO MUCH!!!!

You made my hell job a little bit more confortable


Report •


Ask Question