visual basic script --> applescript

S

sycsummit

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I had this code written for me by a person who I am no longer in touch with. Unfortunately, new computer, same application, and the VB macros won't run. Any applescript wizards out there able to re-write these 2 macros for me? (or give me ultra-dumbed-down instruction on how to do it myself...?) --I've numbered these "1 -" and "2 -"; the numbered lines are not included in the actual VB code.

1 - "moveuniquenames":

Sub MoveUniqueNames()
  Dim X As Long
  Dim Y As Long
  Dim Z As Long
  Dim LastCell As Long
  Dim Total As Double
  Dim UniqueNames As String
  Const SourceColumn As String = "J"
  Const SourceStartRow As Long = 4
  Const DestinationColumn As String = "A"
  Const DestinationStartRow As Long = 5
  Const SourceMoneyColumn As String = "I"
  Const DestinationMoneyColumn As String = "B"
  Const SourceSheet As String = "NEW"
  Const UniqueSheet As String = "Billing"
  UniqueNames = "*"
  Z = DestinationStartRow
  With Worksheets(SourceSheet)
    LastCell = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
    For X = SourceStartRow To LastCell
      If .Cells(X, SourceColumn) "" Then
        If InStr(UniqueNames, "*" & _
                 .Cells(X, SourceColumn).Value & "*") = 0 Then
          UniqueNames = UniqueNames & .Cells(X, SourceColumn).Value & "*"
          Worksheets(UniqueSheet).Cells(Z, DestinationColumn).Value = _
                                      .Cells(X, SourceColumn).Value
          Z = Z + 1
        End If
      End If
    Next
    For X = DestinationStartRow To Z - 1
      Total = 0
      For Y = SourceStartRow To LastCell
        If .Cells(Y, SourceColumn).Value = Worksheets(UniqueSheet). _
                             Cells(X, DestinationColumn).Value Then
          Total = Total + .Cells(Y, SourceMoneyColumn).Value
        End If
      Next
      Worksheets(UniqueSheet).Cells(X, DestinationMoneyColumn).Value = Total
    Next
  End With
End Sub

Sub MoveUniqueNamesEmb()
  Dim X As Long
  Dim Y As Long
  Dim Z As Long
  Dim LastCell As Long
  Dim Total As Double
  Dim UniqueNames As String
  Const SourceColumn As String = "H"
  Const SourceStartRow As Long = 4
  Const DestinationColumn As String = "A"
  Const DestinationStartRow As Long = 25
  Const SourceMoneyColumn As String = "F"
  Const DestinationMoneyColumn As String = "B"
  Const SourceSheet As String = "EMB ONLY"
  Const UniqueSheet As String = "Billing"
  UniqueNames = "*"
  Z = DestinationStartRow
  With Worksheets(SourceSheet)
    LastCell = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
    For X = SourceStartRow To LastCell
      If .Cells(X, SourceColumn) "" Then
        If InStr(UniqueNames, "*" & _
                 .Cells(X, SourceColumn).Value & "*") = 0 Then
          UniqueNames = UniqueNames & .Cells(X, SourceColumn).Value & "*"
          Worksheets(UniqueSheet).Cells(Z, DestinationColumn).Value = _
                                      .Cells(X, SourceColumn).Value
          Z = Z + 1
        End If
      End If
    Next
    For X = DestinationStartRow To Z - 1
      Total = 0
      For Y = SourceStartRow To LastCell
        If .Cells(Y, SourceColumn).Value = Worksheets(UniqueSheet). _
                             Cells(X, DestinationColumn).Value Then
          Total = Total + .Cells(Y, SourceMoneyColumn).Value
        End If
      Next
      Worksheets(UniqueSheet).Cells(X, DestinationMoneyColumn).Value = Total
    Next
  End With
End Sub

2 - "moveuniquenames":

Sub MoveUniqueNames()
  Dim X As Long
  Dim Y As Long
  Dim Z As Long
  Dim LastCell As Long
  Dim Total As Double
  Dim UniqueNames As String
  Const SourceColumn As String = "J"
  Const SourceStartRow As Long = 4
  Const DestinationColumn As String = "A"
  Const DestinationStartRow As Long = 5
  Const SourceMoneyColumn As String = "I"
  Const DestinationMoneyColumn As String = "B"
  Const SourceSheet As String = "NEW"
  Const UniqueSheet A
 
J

John McGhie

Excel 2008 does not contain VBA. The engine is missing.

There is no way to get VBA to work in Excel 2008.

Next version (2011) will have VBA back, this version does not.

You need to roll back to Excel 2004 if you want that to work.

Cheers


Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I had
this code written for me by a person who I am no longer in touch with.
Unfortunately, new computer, same application, and the VB macros won't run.
Any applescript wizards out there able to re-write these 2 macros for me? (or
give me ultra-dumbed-down instruction on how to do it myself...?) --I've
numbered these "1 -" and "2 -"; the numbered lines are not included in the
actual VB code.

1 - "moveuniquenames":

Sub MoveUniqueNames()
Dim X As Long
Dim Y As Long
Dim Z As Long
Dim LastCell As Long
Dim Total As Double
Dim UniqueNames As String
Const SourceColumn As String = "J"
Const SourceStartRow As Long = 4
Const DestinationColumn As String = "A"
Const DestinationStartRow As Long = 5
Const SourceMoneyColumn As String = "I"
Const DestinationMoneyColumn As String = "B"
Const SourceSheet As String = "NEW"
Const UniqueSheet As String = "Billing"
UniqueNames = "*"
Z = DestinationStartRow
With Worksheets(SourceSheet)
LastCell = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
For X = SourceStartRow To LastCell
If .Cells(X, SourceColumn) "" Then
If InStr(UniqueNames, "*" & _
.Cells(X, SourceColumn).Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, SourceColumn).Value & "*"
Worksheets(UniqueSheet).Cells(Z, DestinationColumn).Value = _
.Cells(X, SourceColumn).Value
Z = Z + 1
End If
End If
Next
For X = DestinationStartRow To Z - 1
Total = 0
For Y = SourceStartRow To LastCell
If .Cells(Y, SourceColumn).Value = Worksheets(UniqueSheet). _
Cells(X, DestinationColumn).Value Then
Total = Total + .Cells(Y, SourceMoneyColumn).Value
End If
Next
Worksheets(UniqueSheet).Cells(X, DestinationMoneyColumn).Value = Total
Next
End With
End Sub

Sub MoveUniqueNamesEmb()
Dim X As Long
Dim Y As Long
Dim Z As Long
Dim LastCell As Long
Dim Total As Double
Dim UniqueNames As String
Const SourceColumn As String = "H"
Const SourceStartRow As Long = 4
Const DestinationColumn As String = "A"
Const DestinationStartRow As Long = 25
Const SourceMoneyColumn As String = "F"
Const DestinationMoneyColumn As String = "B"
Const SourceSheet As String = "EMB ONLY"
Const UniqueSheet As String = "Billing"
UniqueNames = "*"
Z = DestinationStartRow
With Worksheets(SourceSheet)
LastCell = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
For X = SourceStartRow To LastCell
If .Cells(X, SourceColumn) "" Then
If InStr(UniqueNames, "*" & _
.Cells(X, SourceColumn).Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, SourceColumn).Value & "*"
Worksheets(UniqueSheet).Cells(Z, DestinationColumn).Value = _
.Cells(X, SourceColumn).Value
Z = Z + 1
End If
End If
Next
For X = DestinationStartRow To Z - 1
Total = 0
For Y = SourceStartRow To LastCell
If .Cells(Y, SourceColumn).Value = Worksheets(UniqueSheet). _
Cells(X, DestinationColumn).Value Then
Total = Total + .Cells(Y, SourceMoneyColumn).Value
End If
Next
Worksheets(UniqueSheet).Cells(X, DestinationMoneyColumn).Value = Total
Next
End With
End Sub

2 - "moveuniquenames":

Sub MoveUniqueNames()
Dim X As Long
Dim Y As Long
Dim Z As Long
Dim LastCell As Long
Dim Total As Double
Dim UniqueNames As String
Const SourceColumn As String = "J"
Const SourceStartRow As Long = 4
Const DestinationColumn As String = "A"
Const DestinationStartRow As Long = 5
Const SourceMoneyColumn As String = "I"
Const DestinationMoneyColumn As String = "B"
Const SourceSheet As String = "NEW"
Const UniqueSheet A
Click to expand...

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting Word macro for the Mac 1
Macro for combining Word documents into one —converting PC macro 2
coding error I don't know how to debug? 0
Create a list that is a string. 1
Script out of range 8
Compare two different workbook sheet to show the difference in text 0
How to Search two column value are match 0
Subscript out of range 0

Top