VBA Question - Excel

[jwwhite021]

Hi,

My friend created an excel file using VBA so that I could manage my investments, there is a macro that I use to enter a transaction, it would take the details I enter, like ticker, number of shares sold and transfer that data into another excel tab. It has historically added the transaction at the bottom of the list e.g. row 21, then 22, then 23 etc, but for some reason it now enters it at the top and it is ruining this rather complex file.

When I click on debug, the following code is highlighted:
highestRef = Sheets("Trading ledger").Range(tradingLedgerCellStart).Offset(l, NbField + 4).Value

I have no idea what the above code is saying so I am hoping that one of your could a) translate and then b) suggest a potential new code?

Many thanks for your help.

John

 

[DiscoPistol]

2 things you can do to help us help you out.

(1) hover over the highlighted area and see what value has been given (if any) to 'highestref'
(2) search the project to see where else 'highestref' is mentioned.

I would guess that its supposed to be the next blank row but can't be sure without more info.

DP

 

[RoryA]

a) That means:
tradingLedgerCellStart is a cell address, or the name of a named range on the 'Trading Ledger' sheet
start at that cell and go down l rows and across (NbField + 4) columns, then assign the value of that cell to the highestRef variable.
b) There is no realistic way for us to suggest an alternative code given that we know nothing about how your sheets are laid out or what those variables represent. Clearly something has changed in your workbook (probably the insertion or deletion of some columns I would suspect).

 

[Andrew Poulsom]

How are tradingLedgerCellStart, l and NbField assigned values and what are those values when you run your code?

 

[Jubjab]

It sounds like to macro is working (although not with the desired output), so why does it highlight a particular row? Or is there an error which points you to this row?

If you hover your mouse over the variables (highestRef, tradingLedgerCellStart, l, NbField), you will see which values they are assigned. That might give you a clue on what is going wrong.

 

[jwwhite021]

Firstly, thank you for getting back to me,

The full code for this is as follows

'Assign a deal reference
highestRef = -1
For l = 1 To nbtrade - 1
If Sheets("Trading ledger").Range(tradingLedgerCellStart).Offset(l, NbField + 4).Value > highestRef Then
highestRef = Sheets("Trading ledger").Range(tradingLedgerCellStart).Offset(l, NbField + 4).Value
End If
Background - each transaction is assigned a deal reference, the text highlighted in red seems to be the error. When I hover over, I get the following results:
highestref=0,
TradingLedgerCell Start = A8
Offset = i=2, nbfield=8

So historically this code has transferred the data I would enter in one tab in to a row on another tab and assign a deal reference number.(which is reflected in the code above).

What would be useful is to correct the code above so that it assigns a reference greater than that last one by one, and then places the info below the last data row.

Hope this helps you, thanks again!

John

 

[jwwhite021]

any help!?