In Excel how to write a search and replace in VBA 1

[Minimorgie]

Hi,

I have columns of financial data and I need to search for a cost code / value combination and where certain conditions are met I need to carry out a replace. Here's some sample data :

cost centre cost account Period Balance date
1002111 1100 186000.00 19/12/2007
1002111 1110 272372.66 19/12/2007
1002111 1120 4030.31 19/12/2007
1002111 1180 3133.33 19/12/2007
1002111 1185 775.50 19/12/2007
1002111 1220 368.00 19/12/2007
1002111 1510 -176000.00 19/12/2007

Where certain cost accounts appear and the period balance is a negative I need to replace the minus sign. I've written the following if statement to identify the correct cost accounts and where the value is < 0 but I don't know how to then replace the minus sign.

=IF(AND(C7<0,(OR(B7="1510",B7="1530",B7="1305",B7="1515",B7="1540",B7="1550"))),"true")

Any help would be very much appreciated.

Thanks

Minimorgie

 

[Loomah]

hi
if you're looking to do this using a worksheet function then the ABS function may well be the way to go.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Minimorgie]

I see what you're saying with the ABS function but that's not really the bit I'm stuck on. I need to know how to write the macro or VBA to search for the values which are less than zero AND have a cost account equal to one of those in my list. If these conditions are met then I need to replace the minus sign. There will be cases where there is a minus sign for a different cost account which should remain as it is.

Thanks

Minimorgie

 

[Loomah]

have you tried anything yet?

a REALLY good place is the help file for "find" which gives a really useful example which you could incorporate into another loop to move through all the cost accounts you wish to look at.

in the meantime i'll throw something together for you

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

here we go - something to work from

Sub mit()
Dim c_1 As Range
Dim firstcell As String
Dim accs, acc As Integer


accs = Array(1510, 1530, 1305, 1515, 1540, 1550)
For acc = LBound(accs) To UBound(accs)
    With Worksheets(1).Range("e3:e9")   ' change as necessary
    Set c_1 = .Find(accs(acc), LookIn:=xlValues)
    If Not c_1 Is Nothing Then
        firstcell = c_1.Address
        Do
            If c_1.Offset(0, 1).Value < 0 Then c_1.Offset(0, 1).Value = Abs(c_1.Offset(0, 1).Value)
            Set c_1 = .FindNext(c_1)
        Loop While Not c_1 Is Nothing And c_1.Address <> firstcell
    End If
End With
Next


End Sub

you'll notice the reliance on the example code from the VBA help files!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Minimorgie]

Hi,

After a bit of fiddling around I got this code to work so many thanks for your help. Incidently, I checked out the help and whilst I can see that the basis of your code came from the help script, there's no way I would have been able to work it out. This is only my second ever VB script so I have little knowledge.

Thanks again for the help.

 

[Loomah]

Ah, it sometimes helps when you say you're new to VBA (though not always!) Glad I could help though, and this is one way to learn - find the solution and try to figure out what it does then use forums like this one for further help and clarification.

Good Luck!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​