Macro help (Yep, another one!!!)

[thunderfoot]

I don't have BA connected to EXCEL, and all I'm doing is adding a letter to the end of the event details in cell A1. all the cells in range B5:G10 show 0 (zero), F2 shows 'Suspended' and as cell A1 does not equal cell A24 (because I'm adding a letter to the end) I would expect it to trigger my Copy/Paste/Sort routine. I've tried it with the code If Target.Columns.Count <> 16 Then Exit Sub and done as Mitch suggested (I highlighted cells A11:P11 and pressed 'Delete'), and I also tried it with the code editted out, and I still can't get the code to trigger the Copy/Paste/Sort routine.

[GeorgeUK]

What values do you get when you step through the code?

try this:
On the line {If Target.Columns.Count <> 16 Then Exit Sub} selct it and press F9 (turning it brown)
go back to the worksheet and do Mitch's suggestion of selecting the 16 columns and pressing delete.
(the worksheet change macro should fire and bring up the vba at the break line).
Put your cursor over target.columns.count
(this should read 16 as you have done the delete on 16 columns)
press F8 to go through the rest of the lines to ensure they are not coming up as false and ending the sub.

how do you get on with this?

[thunderfoot]

What values do you get when you step through the code?

try this:
On the line {If Target.Columns.Count <> 16 Then Exit Sub} selct it and press F9 (turning it brown)
go back to the worksheet and do Mitch's suggestion of selecting the 16 columns and pressing delete.
(the worksheet change macro should fire and bring up the vba at the break line).
Put your cursor over target.columns.count
(this should read 16 as you have done the delete on 16 columns)
press F8 to go through the rest of the lines to ensure they are not coming up as false and ending the sub.

how do you get on with this?

Hi G,
Yep, it does read 16. Pressing F8 jumps from

Code: Select all

If Range("F5") = "Suspended" Then

to the last 'End If' statement.
F5 = "Suspended";B5:G10 = 0 and A1<>A24, so I would have expected the code to go to

Code: Select all

If WorksheetFunction.Sum(Range("B5:G10")) = 0 Then

and

Code: Select all

If Range("A1") <> Range("A24") Then

[GeorgeUK]

What if you try defining it as text?

aa = Range("F5").text
If Range("F5").text = "Suspended" then

If you put this, then you should be able to see what the code is saying F5 is from hovering over aa
See if this helps, and also check there isn't a spelling mistake or extra space in the cell

[thunderfoot]

What if you try defining it as text?

aa = Range("F5").text
If Range("F5").text = "Suspended" then

If you put this, then you should be able to see what the code is saying F5 is from hovering over aa
See if this helps, and also check there isn't a spelling mistake or extra space in the cell

aa="0.00". F5 definitely reads Suspended.

Pressing F8 does as above, goes to

Code: Select all

If Range("F5").Text = "Suspended" Then

then jumps to the last 'End If' statement!!!

[thunderfoot]

PRATT!!!!! It's not F5 it's F2 in'it!!!


Anyway, I still can't get it to work

In cell C20 I have the formula =IF(AND(SUM(B5:G16)=0,F2="Suspended",A24<>A1),1,0) to check the 3 scenarios I want to be TRUE so that this macro could be triggered. Could this formula be used as an alternative method of triggering the macro?

[Ian]

What cell is the start cell for data from BA ? Is it B4 ? "Suspended" would be in E2 if you start in A1.

[Ian]

OK, B1 then ?

[Ian]

I'm looking at an sheet which says in E2 "Not In Play" .. is it this cell that gets changed to "Suspended" ? You could be right about F2.

[Ian]

There's a worksheet_calculate event which is triggered after a calculation so you may be able to just change the event to calculate.

[thunderfoot]

What if you try defining it as text?

aa = Range("F5").text
If Range("F5").text = "Suspended" then

If you put this, then you should be able to see what the code is saying F5 is from hovering over aa
See if this helps, and also check there isn't a spelling mistake or extra space in the cell

Re-booted computer; re-loaded EXCEL; re-loaded Workbook .....
NOW get :
Target.Columns.Count=16 when I hover over the appropriate code.
F8 steps through the code and hovering the mouse over "aa = Range("F2").Text" shows aa=Empty
F8 .... "If Range("F2").Text = "Suspended" Then" shows Range("F2").Text = "Suspended"
F8 .... steps through to "If WorksheetFunction.Sum(Range("B5:G10")) = 0 Then" (mouse hover shows nothing)
F8 ... "If Range("A1") <> Range("A24") Then" (mouse hover shows the value in cell A1)
etc.
etc.
etc.
.... and it worked!!!!!

[thunderfoot]

What cell is the start cell for data from BA ? Is it B4 ? "Suspended" would be in E2 if you start in A1.

Hiya .... shoulda said .... using the non API version to test the macro, so 'Trigger', 'Odds' and 'Stake' are in columns N, O & P not Q, R & S.

[thunderfoot]

Thanks all,

Seems to be working fine now.

Manually changing A1 by adding a letter to the end; highlighting cells A17:P17; pressing delete, and whalla!! it copies/pastes/sorts. ..... and I can't even see-its-lips-move when it does it

If we had the Dancing Cow / Dancing Banana emoticons I'd be using it!!!!!!!

[thunderfoot]

Hiya .... shoulda said .... using the non API version to test the macro, so 'Trigger', 'Odds' and 'Stake' are in columns N, O & P not Q, R & S.

....... and off course, as I'm testing on the 'Trader' version

Code: Select all

   If Target.Columns.Count <> 16 Then Exit Sub


should read

Code: Select all

   If Target.Columns.Count <> 13 Then Exit Sub


[tkp]

This truely must be one of the crazyiest forums on the net.....anyone new coming in and reading some of these dicussions .....the mind boggles as to what they must think.