Maxro Help

[osknows]

There's an error here

IF .Sheets("Venue").Range("Z5:Z44").Value = ""


If what?

[osknows]

Possibly you mean..

IF application.worksheetfunction.countA(.Sheets("Venue").Range("Z5:Z44")) = 0 then
.Sheets("Venue").Range("Z5:Z44").Value = .Sheets("Venue").Range("AA5:AA44").Value

Else

?

[Shaun]

Yeah thats incorrect, i have made to many code changes to even know where i am, i might just search the forum i am sure the answer i need is in here as it is not that hard a task.

Thanks anyways.

[Shaun]

This was the code i was trying to write.

Code: Select all

 
Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

With ThisWorkbook
If .Sheets("Venue").Range("AA2").Value = .Sheets("Venue").Range("AG3").Value Then
   .Sheets("Venue").Range("Z5:Z44").Value = Sheets("Venue").Range("AA5:AA44").Value
   
Else
   
    If [A1].Value = MyMarket Then
        GoTo Xit
    Else
        MyMarket = [A1].Value
        .Sheets("Venue").Range("Z5:Z44").Value = ""
        .Sheets("Venue").Range("Z5:Z44").Value = Sheets("Venue").Range("AA5:AA44").Value
    End If
   
End If
End With

Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


[osknows]

The code looks fine. The only thing I can think of is possibly on this test

If .Sheets("Venue").Range("AA2").Value = .Sheets("Venue").Range("AG3").Value Then

Either cell AA2 or AG3 has an error as the code runs. Eg if you have a lookup that returns #N/A momentarily?

[Shaun]

AA2= D2 and is formated to time.
AG3 has formula =AF2/1440
AF2 is just a number for example 30 to indicate 30 minutes

[Shaun]

Just to let you know i used this code and it works fine not crash, must have somrthing to do with the Calculate event, not sure what as i have used it in the paste fine.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
If Target.Columns.Count = 16 Then
Application.EnableEvents = False

With ThisWorkbook
If .Sheets("Venue").Range("AA2").Value = .Sheets("Venue").Range("AG3").Value Then
   .Sheets("Venue").Range("Z5:Z44").Value = Sheets("Venue").Range("AA5:AA44").Value
   
Else
   
    If [A1].Value = MyMarket Then
        GoTo Xit
    Else
        MyMarket = [A1].Value
        .Sheets("Venue").Range("Z5:Z44").Value = ""
        .Sheets("Venue").Range("Z5:Z44").Value = Sheets("Venue").Range("AA5:AA44").Value
    End If
   
End If
End With

Xit:
Application.EnableEvents = True
End If
End Sub


[Shaun]

If i wanted to use the sheet code instead of a sheet name is this the correct way to do it.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
With ThisWorkbook
If Sheet11.Range("AA2").Value = Sheet11.Range("AG3").Value Then
   Sheet11.Range("Z5:Z44").Value = Sheet11.Range("AA5:AA44").Value
   
Else
   
    If Sheet11.Range("A1").Value = MyMarket Then
        GoTo Xit
    Else
        MyMarket = Sheet11.Range("A1").Value
        Sheet11.Range("Z5:Z44").Value = ""
        Sheet11.Range("Z5:Z44").Value = Sheet11.Range("AA5:AA44").Value
    End If
    End If
End With
Xit:
Application.EnableEvents = True
End If
End Sub