VBA not working for MAC

F

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
Hi All

I am having an issue where i have a line for a macro which works for PC but not on mac.

The lines provided i'm pretty sure are causing the issue:

Code: 
Set LookUpDate = ActiveCell
On Error GoTo DateNotFound1
Rows(ActiveCell.Row).Cells.Find(What:=LookUpDate, After:=ActiveCell, LookIn:= xlValues, LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

Even tho the date is 11/06/2018 and is there, it will go straight to the error handling.

However when i replace LookUpDate in the find section, with "11/06/2018", it works. Can anyone advise me on this?

(Would copy and paste the vba from the MAC but cant access the internet from it at the moment so is typed out)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Hi Tetra

Thank you for the reply. Unfortunately it is still throwing me to error handling.
 
Upvote 0
The Find Method is looking for a string argument for "what:=". Try:

what:= CStr(LookUpDate)
 
Upvote 0
Hi Joe

Unfortunately that didn't work either.

I will run to another desk so that i can upload the full code just in case i'm missing something elsewhere.
 
Upvote 0
Here we go:

I have also highlighted the area where it goes to error handling.

Sub Summary_Update()


Code: 
'
' Summary_Update Macro
'
'


'3D Work


Application.ScreenUpdating = True
Dim ProjectMinDate As Long
Dim SummaryMinDate As Long
Dim NumofColumns As Long
Dim SummaryMaxDate As Long
Dim ProjectMaxDate As Long
Dim DaysofWork As Variant
Dim ArtistsNeeded As Variant




SummaryMinDate = 99999999
SummaryMaxDate = 1
DaysofWork = 0
ArtistsNeeded = 0
    For i = 4 To Sheets.Count
    Sheets(i).Activate
    Cells.Find(What:="3D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    If Not IsDate(ActiveCell.Value) = False Then
    ProjectMinDate = ActiveCell.Value
    If ProjectMinDate < SummaryMinDate Then
    SummaryMinDate = ProjectMinDate
    End If
    Selection.End(xlToRight).Select
    ProjectMaxDate = ActiveCell.Value
    If ProjectMaxDate > SummaryMaxDate Then
    SummaryMaxDate = ProjectMaxDate
    End If
    End If
    Next
    Sheets(2).Activate
        Cells.Find(What:="3D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    NumofColumns = ((SummaryMaxDate - SummaryMinDate) / 7) - 1
    For J = 1 To NumofColumns + 2
    ActiveCell.Value = SummaryMinDate
[B]    Selection.NumberFormat = "dd/mm/yyyy"[/B]
[B]    Set LookUpDate = ActiveCell[/B]
[B]        For k = 4 To Sheets.Count[/B]
[B]        Sheets(k).Activate[/B]
[B]            Cells.Find(What:="3D Start Date Week Commencing", After:=ActiveCell, _[/B]
[B]            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _[/B]
[B]            SearchDirection:=xlNext, MatchCase:=False).Activate[/B]
[B]            On Error GoTo DateNotFound1[/B]
[B]            Rows(ActiveCell.Row).Cells.Find(What:=CStr(LookUpDate), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate[/B]
            ActiveCell.Offset(1, 0).Range("A1").Select
            DaysofWork = DaysofWork + ActiveCell.Value
            ActiveCell.Offset(4, 0).Range("A1").Select
            ArtistsNeeded = ArtistsNeeded + ActiveCell.Value
Label1:
        Next
            Sheets(2).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = DaysofWork
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = ArtistsNeeded
            ActiveCell.Offset(-2, 1).Range("A1").Select
            ArtistsNeeded = 0
            DaysofWork = 0
    SummaryMinDate = SummaryMinDate + 7
    Next
    
'Moving onto Match Move


ProjectMinDate = 0
SummaryMinDate = 99999999
NumofColumns = 0
SummaryMaxDate = 1
ProjectMaxDate = 0
DaysofWork = 0
ArtistsNeeded = 0
    For i = 4 To Sheets.Count
    Sheets(i).Activate
    Cells.Find(What:="Match Move Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    If Not IsDate(ActiveCell.Value) = False Then
    ProjectMinDate = ActiveCell.Value
    If ProjectMinDate < SummaryMinDate Then
    SummaryMinDate = ProjectMinDate
    End If
    Selection.End(xlToRight).Select
    ProjectMaxDate = ActiveCell.Value
    If ProjectMaxDate > SummaryMaxDate Then
    SummaryMaxDate = ProjectMaxDate
    End If
    End If
    Next
    Sheets(2).Activate
        Cells.Find(What:="Match Move Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    NumofColumns = ((SummaryMaxDate - SummaryMinDate) / 7) - 1
    For J = 1 To NumofColumns + 2
    ActiveCell.Value = SummaryMinDate
    Selection.NumberFormat = "dd/mm/yyyy"
    Set LookUpDate = ActiveCell
        For k = 4 To Sheets.Count
        Sheets(k).Activate
            Cells.Find(What:="Match Move Start Date Week Commencing", After:=ActiveCell, _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False).Activate
            On Error GoTo DateNotFound2
            Rows(ActiveCell.Row).Find(What:=LookUpDate).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            DaysofWork = DaysofWork + ActiveCell.Value
            ActiveCell.Offset(4, 0).Range("A1").Select
            ArtistsNeeded = ArtistsNeeded + ActiveCell.Value
Label2:
        Next
            Sheets(2).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = DaysofWork
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = ArtistsNeeded
            ActiveCell.Offset(-2, 1).Range("A1").Select
            ArtistsNeeded = 0
            DaysofWork = 0
    SummaryMinDate = SummaryMinDate + 7
    Next
    
'Moving Onto Roto Prep


ProjectMinDate = 0
SummaryMinDate = 99999999
NumofColumns = 0
SummaryMaxDate = 1
ProjectMaxDate = 0
DaysofWork = 0
ArtistsNeeded = 0
    For i = 4 To Sheets.Count
    Sheets(i).Activate
    Cells.Find(What:="Roto Prep Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    If Not IsDate(ActiveCell.Value) = False Then
    ProjectMinDate = ActiveCell.Value
    If ProjectMinDate < SummaryMinDate Then
    SummaryMinDate = ProjectMinDate
    End If
    Selection.End(xlToRight).Select
    ProjectMaxDate = ActiveCell.Value
    If ProjectMaxDate > SummaryMaxDate Then
    SummaryMaxDate = ProjectMaxDate
    End If
    End If
    Next
    Sheets(2).Activate
        Cells.Find(What:="Roto Prep Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    NumofColumns = ((SummaryMaxDate - SummaryMinDate) / 7) - 1
    For J = 1 To NumofColumns + 2
    ActiveCell.Value = SummaryMinDate
    Selection.NumberFormat = "dd/mm/yyyy"
    Set LookUpDate = ActiveCell
        For k = 4 To Sheets.Count
        Sheets(k).Activate
            Cells.Find(What:="Roto Prep Start Date Week Commencing", After:=ActiveCell, _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False).Activate
            On Error GoTo DateNotFound3
            Rows(ActiveCell.Row).Find(What:=LookUpDate).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            DaysofWork = DaysofWork + ActiveCell.Value
            ActiveCell.Offset(4, 0).Range("A1").Select
            ArtistsNeeded = ArtistsNeeded + ActiveCell.Value
Label3:
        Next
            Sheets(2).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = DaysofWork
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = ArtistsNeeded
            ActiveCell.Offset(-2, 1).Range("A1").Select
            ArtistsNeeded = 0
            DaysofWork = 0
    SummaryMinDate = SummaryMinDate + 7
    Next
    
' Moving Onto DMP


ProjectMinDate = 0
SummaryMinDate = 99999999
NumofColumns = 0
SummaryMaxDate = 1
ProjectMaxDate = 0
DaysofWork = 0
ArtistsNeeded = 0
    For i = 4 To Sheets.Count
    Sheets(i).Activate
    Cells.Find(What:="DMP Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    If Not IsDate(ActiveCell.Value) = False Then
    ProjectMinDate = ActiveCell.Value
    If ProjectMinDate < SummaryMinDate Then
    SummaryMinDate = ProjectMinDate
    End If
    Selection.End(xlToRight).Select
    ProjectMaxDate = ActiveCell.Value
    If ProjectMaxDate > SummaryMaxDate Then
    SummaryMaxDate = ProjectMaxDate
    End If
    End If
    Next
    Sheets(2).Activate
        Cells.Find(What:="DMP Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    NumofColumns = ((SummaryMaxDate - SummaryMinDate) / 7) - 1
    For J = 1 To NumofColumns + 2
    ActiveCell.Value = SummaryMinDate
    Selection.NumberFormat = "dd/mm/yyyy"
    Set LookUpDate = ActiveCell
        For k = 4 To Sheets.Count
        Sheets(k).Activate
            Cells.Find(What:="DMP Start Date Week Commencing", After:=ActiveCell, _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False).Activate
            On Error GoTo DateNotFound4
            Rows(ActiveCell.Row).Find(What:=LookUpDate).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            DaysofWork = DaysofWork + ActiveCell.Value
            ActiveCell.Offset(4, 0).Range("A1").Select
            ArtistsNeeded = ArtistsNeeded + ActiveCell.Value
Label4:
        Next
            Sheets(2).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = DaysofWork
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = ArtistsNeeded
            ActiveCell.Offset(-2, 1).Range("A1").Select
            ArtistsNeeded = 0
            DaysofWork = 0
    SummaryMinDate = SummaryMinDate + 7
    Next
    
' Moving Onto Comp


ProjectMinDate = 0
SummaryMinDate = 99999999
NumofColumns = 0
SummaryMaxDate = 1
ProjectMaxDate = 0
DaysofWork = 0
ArtistsNeeded = 0
    For i = 4 To Sheets.Count
    Sheets(i).Activate
    Cells.Find(What:="Comp Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    If Not IsDate(ActiveCell.Value) = False Then
    ProjectMinDate = ActiveCell.Value
    If ProjectMinDate < SummaryMinDate Then
    SummaryMinDate = ProjectMinDate
    End If
    Selection.End(xlToRight).Select
    ProjectMaxDate = ActiveCell.Value
    If ProjectMaxDate > SummaryMaxDate Then
    SummaryMaxDate = ProjectMaxDate
    End If
    End If
    Next
    Sheets(2).Activate
        Cells.Find(What:="Comp Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    NumofColumns = ((SummaryMaxDate - SummaryMinDate) / 7) - 1
    For J = 1 To NumofColumns + 2
    ActiveCell.Value = SummaryMinDate
    Selection.NumberFormat = "dd/mm/yyyy"
    Set LookUpDate = ActiveCell
        For k = 4 To Sheets.Count
        Sheets(k).Activate
            Cells.Find(What:="Comp Start Date Week Commencing", After:=ActiveCell, _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False).Activate
            On Error GoTo DateNotFound5
            Rows(ActiveCell.Row).Find(What:=LookUpDate).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            DaysofWork = DaysofWork + ActiveCell.Value
            ActiveCell.Offset(4, 0).Range("A1").Select
            ArtistsNeeded = ArtistsNeeded + ActiveCell.Value
Label5:
        Next
            Sheets(2).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = DaysofWork
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = ArtistsNeeded
            ActiveCell.Offset(-2, 1).Range("A1").Select
            ArtistsNeeded = 0
            DaysofWork = 0
    SummaryMinDate = SummaryMinDate + 7
    Next


    Exit Sub
    
DateNotFound1:
    Resume Label1:
DateNotFound2:
    Resume Label2:
DateNotFound3:
    Resume Label3:
DateNotFound4:
    Resume Label4:
DateNotFound5:
    Resume Label5:
    




'
End Sub
 
Upvote 0
Your LookUpDate is a range object with a .Value default property. Not sure why you define it that way, but could be part of the issue. Try changing this line:

Set LookUpDate = ActiveCell

to this:

LookUpDate = ActiveCell.Text

and use: what:= LookUpDate
 
Upvote 0
Hi Joe

Unfortunately this didnt help either.

As to why i done this, my only excuse would be i'm new to VBA so i googled and tested things until they worked.
 
Upvote 0
What happens with

Code: 
LookUpDate = CLng(ActiveCell.Value)
 
Upvote 0
Hi mark

Thank you for your reply

I will try this first thing tomorrow morning and let you know. Its the end of the day and i have to get home
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
cells.find error handling
2
Replies
13
Views
604
jolivanes
jolivanes
S
  • Question Question
Find 1st blank row to fill in when there is an active filter
2
Replies
11
Views
273
Alex Blakenburg
A
L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
339
Linda Lo
L
SlinkRN
  • Solved
Handling Multiple Errors within one loop
Replies
7
Views
289
SlinkRN
SlinkRN
S
  • Solved
Search for entered value in another tab
Replies
3
Views
130
skittlz
S

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top