Need to find numbers in column formatted as currency

[renigar]

At work we have a vendor program that we use to export reports to excel (the canned reports are less than helpful). The format of the export requires modification to be useful. I've attached a small screen shot sample. In column A excel shows everything formatted as general, except dollar amounts are as currency. The other number in the column is a location ID. With the code I want to insert a blank row after each row where column A has a currency value. The piece of code dealing with this follows:

Dim LastRow As Double
Dim RowCtr As Double
LastRow = 2000
For RowCtr = 1 To LastRow
If Cells(RowCtr, "A") = ??Currency?? Then
Rows(RowCtr + 1).Insert
RowCtr = RowCtr + 1
End If
Next RowCtr

I think I need to use numberformat but I'm not sure how to apply it in this situation. I tried just using 0 where ??Currency?? is because I thought the value always was 0, but I was wrong. I'm using Excel 2007.

Thank you,
renigar

 

[macropod]

If it's just $ amounts, you could test with:

For RowCtr = LastRow To 1 Step -1
  If Left(Cells(RowCtr, "A").Text, 1) = "$" Then
    Rows(RowCtr + 1).Insert
  End If
Next RowCtr

For currencies generally, you might use something like:

For RowCtr = LastRow To 1 Step -1
  Select Case Left(Cells(RowCtr, "A").Text, 1)
    Case "$", "€", "£", "¥"
    Rows(RowCtr + 1).Insert
  End Select
Next RowCtr

Note that the code processes the range in reverse order. Amongst other things, this obviates the need to keep resetting the RowCtr variable.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

hi,

People who write report exports for Excel like that are ignorant!

1. There ore not headings

2. There is no tabular column integrity

The file is virtually useless without significant restructuring.

The KEY to restructuring is to determine how to programmatically identify each row as a record type and then map it accordingly.

I see 3 types: Account, Detail, Summary. Each Account row has a numeric account number in column A. Each Detail has one or more rows with a TEXT value in column A. Each Summary row has no value in column A.

Each of these types ought to be mapped to a new sheet in their own columns so that with the data as displayed, you would have a table with 22 columns, each with it's own heading, like AccountNbr. Then you would have a proper table that would be useful and not just take up space.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Skip,

Now that you've finished ventilating, the OP did say:

At work we have a vendor program that we use to export reports to excel (the canned reports are less than helpful). The format of the export requires modification to be useful.

From what's been posted, the data layout's not hard to restructure - it's just a nuisance having to do so.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

Reports like that set off a fuse in my brain, and it burned right down short of the explosive.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renigar]

Thanks macropod,
I used your first example and it worked perfectly for me. And Skip I agree with you and get irritated everytime I have to crunch some numbers from these exports. I was about to vent but will refrain. Anyway things will be much easier since my macro is complete and works as desired. Thanks again for the help!
renigar

 

[MazeWorX]

Then you would LOVE the company I work for Skip

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>