• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA & MacOS

makis

New Member
Hello all,

First I am not fluid with VBA.
But, one of my client uses Mac ...
I know that VBA runs on Mac , but this one no.
Here are version number both Excel and MacOS
74902

74903
As you can see Office 365 is paid.

The error is
74904

and the new one

74905

The VBA Code is

VBA Code:
>>> use code - tags <<<
Code:
Sub BuildInvoiceAll()
  Dim ws As Variant, arr1 As String, arr2 As String, arr3 As String, arr4 As String, arry As Variant
  Dim i As Long, j As Long, nr As Long
  Dim cell As Range, f As Range
  Dim Descript As String
 
  Application.ScreenUpdating = False
  'Set array of worksheet names to copy from
  ws = Array("AUDIO", "LIGHTS", "HOISTS - TRUSS - DRAPES", "DISTRO - CABLES - MISC")

  'cells to AUDIO sheet
  arr1 = "E13:E43, J13:J30, J32:J43, E57:E84, J57:J84, E100:E131, J100:J107," & _
        "J109:J118, J120:J131, E146:E176, E178:E191, J146:J176, J178:J184, J186:J197"
  'cells to LIGHTS sheet
  arr2 = "E13:E34, J13:J59, E36:E59, E73:E89, J73:J82, J84:J91, E91:E98, J93:J101, E100:E109, J103:J113"
  'cells to HOISTS sheet
  arr3 = "E13:E28, K13:K37, E30:E40, E42:E52, E67:E91, K67:K85, E106:E123, K106:K119, K121:K129, E127:E137"
  'cells to DISTRO sheet
  arr4 = "E13:E35, K13:K50, E37:E50, E64:E116, K64:K88, K92:K108, K111:K120, E131:E148, K131:K148, K150:K159," & _
         "E152:E180 , K163:K188 , K190:K203 , E184:E216 , K207:K238 , K240:K249 "
  arry = Array(arr1, arr2, arr3, arr4)
  nr = 14
  Sheets("PROFORMA DRYHIRE").Range("A15:C70").ClearContents
  For i = LBound(ws) To UBound(ws)                  'Loop through all shees in the array
    For Each cell In Sheets(ws(i)).Range(arry(i))   'Loop through all cells in the multirange
      If cell > 0 Then                              'See if anything entered in pieces
        Descript = cell.Offset(0, -3)               'get description from column B
        With Sheets("PROFORMA DRYHIRE")
          Set f = .Range("A15:A70").Find(Descript, , xlValues, xlWhole)
          If Not f Is Nothing Then
            nr = f.Row
          Else
            nr = nr + 1
            If nr > 70 Then
              MsgBox "Rows are full"
              Exit Sub
            End If
          End If
          .Cells(nr, "A") = Descript                'Populate values in PROFORMA sheet
          .Cells(nr, "B") = cell                    'get pieces from column E
          .Cells(nr, "C") = cell.Offset(0, -1)      'get price p/d from column D
        End With
      End If
    Next cell
  Next i
  Application.ScreenUpdating = False
End Sub

Sub ClearContentsAUDIO()
'   Clear All contents from Sheet - AUDIO
    Worksheets("AUDIO").Range("E13:E43, J13:J30, J32:J43, E57:E84, J57:J84, E100:E131, J100:J107, J109:J118, J120:J131, E146:E176, E178:E191, J146:J176, J178:J184, J186:J197").ClearContents
    MsgBox "Ç öüñìá AUDIO êáèÜñéóå!"
End Sub

Sub ClearContentsLIGHTS()
'   Clear All contents from Sheet - LIGHTS
    Worksheets("LIGHTS").Range("E13:E34, J13:J59, E36:E59, E73:E89, J73:J82, J84:J91, E91:E98, J93:J101, E100:E109, J103:J113").ClearContents
    MsgBox "Ç öüñìá LIGHTS êáèÜñéóå!"
End Sub

Sub ClearContentsHOIST()
'   Clear All contents from Sheet - HTD
    Worksheets("HOISTS - TRUSS - DRAPES").Range("E13:E28, K13:K37, E30:E40, E42:E52, E67:E91, K67:K85, E106:E123, K106:K119, K121:K129, E127:E137").ClearContents
    MsgBox "Ç öüñìá HTD êáèÜñéóå!"
End Sub

Sub ClearContentsDISTRO()
'   Clear All contents from Sheet - DCM
    Worksheets("DISTRO - CABLES - MISC").Range("E13:E35, K13:K50, E37:E50, E64:E116, K64:K88, K92:K108, K111:K120, E131:E148, K131:K148, K150:K159, E152:E180 , K163:K188 , K190:K203 , E184:E216 , K207:K238 , K240:K249 ").ClearContents
    MsgBox "Ç öüñìá DCM êáèÜñéóå!"
End Sub
What is it wrong? There are no redirects or mysterious coding.
Can someone give me a hint of what to change?

(texts are in Greek)

I have also upload the excel file to test

Thank you in advance!
 

Attachments

Last edited by a moderator:

vletm

Excel Ninja
makis
What are those first five ... ?
Do You need those?
If Yes ... then try to rename and use western alphabets
If No ... then try to delete
For me - Your code gives
Invalid character.
Screenshot 2021-06-04 at 18.37.24.png
 

makis

New Member
@vletm I cant see first five!

I only see

74909

Can I unhide them or anything else?

I attached the same but changed everything to English.
Can you please try this?

Thank you !
 

Attachments

Last edited:

vletm

Excel Ninja
makis
Above Book1.xlsm is different file.
That file misses all codes.
After paste code from Your original file - I didn't get any errors.
What is Your point?
Ps. Your snapshot ... do it look like everything in English?
 

makis

New Member
@vletm

I just copy-paste sheets and the VBA code to another worksheet, the one I uploaded last book1.xlsm
Is it possible to upload the one you copied the code?

Are you trying it on MacOS? Is it working ?
Sorry for asking all these I am trying to learn as you understand
 

vletm

Excel Ninja
makis
Yes ... I see.
I did as I wrote and tried to run that code from code-page with Mac.
If You've done ... those ... copy&paste ... then there will be one basic (unwanted) feature. ( Copy&paste could do correct way too. )
You should Assign Macros to those buttons one-by-one.
> Activate with second-mouse-button one Your sheets 'control-button' and Assign correct Macro to it.
>> or >> modify that path of Macro.

I didn't modify those while I did my tests - that wasn't Your point.
Ps. I didn't check code ... there seems to be something, which could do other ways.
HInt: Your client would update that Mac too. There is a newer version.
 

makis

New Member
@vletm
Yes I know about the macro and yes I have made all the corrections.
So, the excel is working on Mac ??? vba and everything?
I am a windows user, is there a way to test my files if they run on Mac?
What is the correct way for copy paste? An what modification I can do with the code?
Thank you for your help!
 

vletm

Excel Ninja
makis
Excel is working with Mac.
There are differences.
You should have Mac to test.
Copy&paste ... the basic idea is that You should know what do You want to paste.
For many cases, You would like to copy only values.
In Your case - You copied those buttons too - but - You didn't take care those paths.
To do with the code - I won't start to modify others code.
You could reread those codes and check/notice Yourself.
 

makis

New Member
ok fair. I dont want you to modify the code, the basic idea for my post to check if someone that using mac to check if the code works with macos.
And if you say that is working fine, then I am ok with that, and thank you very much.
About the buttons. ok my fault I didnt check the path, thats why when I pressed it it opens the first excel...

About the MacOS , results everything are working if , lets say, enter some quantities to AUDIO, or LIGHTS equipment and press the INSERT / CHECK button on PROFORMA then there is no error.

Thank you very much @vletm I really appreciate your help.
ps. is it possible to send me a screenshot of the working MAC ?
ps2. I have uploaded the file again, can you check this?
 

Attachments

Last edited:

vletm

Excel Ninja
makis
I cannot comment do it work fine - I could comment that it won't give error.
There seems to possible to write in every cell - that would need to notice for user.
As well as - anybody could add & delete row & column --- those will do something unwanted with Your code.
... working MAC ... one frozen snapshot?
Screenshot 2021-06-05 at 12.10.39.png
You seems to moved some codes ... I have other logic.
 
Top