Help with VBA: Inserting formula with loop

[sam82]

Hello,

I import this Excel file that has amounts down one column, and the words buy/sell down two columns over to tell which of those amounts are buy and sell. Unfortunately, buys are not shown as a negative amount (ex: -2,000, or (2,000)). They both show as positive amounts. I'm trying to use VBA to make a formula that will look to see if it's a buy transaction, copy the amount over, and make it negative. I need it to ignore sell transactions as it moves down. Here's what I have:

Sub BuySell()
Range("F2").Select
Buy = 2
Do While ActiveCell.Value <> 0
If ActiveCell.Value = "Buy" Then
ActiveCell.Offset(0, -2).Select
Selection.Copy
ActiveCell.Offset(0, 3).Select 'Range G2
Selection.Paste 'Range G2
ActiveCell.Offset(0, 1).Select 'Range H2
ActiveCell.Formula = "=(G" & Buy & "*-1)"
ActiveCell.Offset(0, -2).Select
Buy = Buy + 1
Else
ActiveCell.Offset(1, 0).Select
Loop
End If

I use to be good in VBA but it's been over two years since I used it last and I can't get this to work!

Thanks,

 

[Norie]

So you have Buy/Sell in column F and you want to copy the value in column D of the same row to column G and put a formula in column H.

Perhaps this?

Dim LastRow As Long
LastRow = Range("F" & Rows.Count).End(xlUp).Row

Range("G2:G" & LastRow).Formula = "=IF(F2=""BUY"", D2, """")"
Range("H2:H" & LastRow).Formula = "=IF(F2=""BUY"", -D2, """")"

 

[sam82]

*facepalm*

..........brilliant.

Thanks!