New To Mac-Forums?

Welcome to our community! Join the discussion today by registering your FREE account. If you have any problems with the registration process, please contact us!

Get your questions answered by community gurus Advice and insight from world-class Apple enthusiasts Exclusive access to members-only contests, giveaways and deals

Join today!

 
Start a Discussion
 

Mac-Forums Brief

Subscribe to Mac-Forums Brief to receive special offers from Mac-Forums partners and sponsors

Join the conversation RSS
OS X - Operating System General OS operation information and support

I have a VBA issue


Post Reply New Thread Subscribe

 
Thread Tools
MisterMo

 
Member Since: May 28, 2012
Posts: 1
MisterMo is on a distinguished road

MisterMo is offline
I am trying to run a VBA in excel in Lion/excel 2011 that has been written in Windows 7/Excel 2010.
It is giving me a Run-time error "448"
Named argument not found

Module 1:

Option Explicit
'Show UserForm
Sub ShwFrm()
UserForm1.Show
End Sub

'Calculate and populate new properties
Sub PopProperties()
Application.ScreenUpdating = False
Dim PctDone As Single
'Declarations
Dim SheetName As String
Dim StrM As Variant, EndM As Variant, StrCol As Long, MthRw As Long, i As Long, EndCol As Long
Dim AvailBalRw As Long, NewPropRw As Long
'Initializations
SheetName = "Model_Summary_Fund(I)"
MthRw = 15
AvailBalRw = 19
NewPropRw = 20
StrM = Sheets(SheetName).Range("E12").Value
EndM = Sheets(SheetName).Range("J12").Value
'Mandatory field Validations
If StrM = "" Then
Unload UserForm1
MsgBox "Please enter ""Start Max Purchase Month"" and continue.", vbCritical, "Start Month Required"
Exit Sub
ElseIf EndM = "" Then
Unload UserForm1
MsgBox "Please enter ""Finish Max Purchase Month"" and continue.", vbCritical, "Finish Month Required"
Exit Sub
End If
'Numeric Validations
If IsNumeric(StrM) = False Then
Unload UserForm1
MsgBox """Start Max Purchase Month"" should be a Number.", vbCritical, "Invalid Start Month"
Exit Sub
ElseIf IsNumeric(EndM) = False Then
Unload UserForm1
MsgBox """Finish Max Purchase Month"" should be a Number.", vbCritical, "Invalid Finish Month"
Exit Sub
End If
'Find Column Number of Start Month and End Month in Row 15
StrCol = FindCol(SheetName, MthRw, StrM)
EndCol = FindCol(SheetName, MthRw, EndM)
'Check if Start Month and End Month Column exist in the WorkSheet
If StrCol = 0 Then
Unload UserForm1
MsgBox "The entered ""Start Max Purchase Month"" cannot be found.", vbCritical, "Start Month does not exist"
Exit Sub
ElseIf EndCol = 0 Then
Unload UserForm1
MsgBox "The entered ""Finish Max Purchase Month"" cannot be found.", vbCritical, "Finish Month does not exist"
Exit Sub
End If
'If all good, loop through the Columns, calculate and populate the number of new properties
For i = StrCol To EndCol
Call CalNewProp(SheetName, AvailBalRw, NewPropRw, i)
'For Progress bar
PctDone = i / EndCol
UpdateProgressBar PctDone
Next
Unload UserForm1
MsgBox "New Properties populated successfully. Happy days!", vbInformation, "Success"
Application.ScreenUpdating = True
End Sub
'Calculate number of new properties
Sub CalNewProp(SheetName, AvailBalRw, NewPropRw, MthCol)
Dim lCount As Long, AvailBal As Double
lCount = 0
Do
lCount = lCount + 1
Sheets(SheetName).Cells(NewPropRw, MthCol).Value = lCount
AvailBal = Sheets(SheetName).Cells(AvailBalRw, MthCol).Value
Loop While AvailBal >= 0
Sheets(SheetName).Cells(NewPropRw, MthCol).Value = lCount - 1
End Sub

'Find the Column Numbers of Start and Finish Month
Function FindCol(SheetName, FRw, FindVal)
Dim rFound As Range
'On Error Resume Next
Set rFound = Sheets(SheetName).Rows(FRw).Find(What:=FindVal, After:=Sheets(SheetName).Cells(FRw, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
'On Error GoTo 0
If Not rFound Is Nothing Then
FindCol = rFound.Column
Exit Function
Else
FindCol = 0
End If
End Function
'Update the progress bar
Sub UpdateProgressBar(PctDone As Single)
With UserForm1
' Update the Caption property of the Frame control.
.FrameProgress.Caption = Format(PctDone, "0%")
' Widen the Label control.
.LabelProgress.Width = PctDone * _
(.FrameProgress.Width - 10)
End With
' The DoEvents allows the UserForm to update.
DoEvents
End Sub


User Form:

'Call Populate function
Private Sub UserForm_Activate()
' Set the width of the progress bar to 0.
UserForm1.LabelProgress.Width = 0
' Call the main subroutine.
If ThisWorkbook.ActiveSheet.Name = "Model_Summary_Fund(I)" Then
Call Module1.PopProperties
ElseIf ThisWorkbook.ActiveSheet.Name = "Model_Summary_Fund(2)" Then
Call Module2.PopProperties
ElseIf ThisWorkbook.ActiveSheet.Name = "Model_Summary_SS" Then
Call Module3.PopProperties
End If
End Sub
QUOTE Thanks
chscag

 
chscag's Avatar
 
Member Since: Jan 23, 2008
Location: Fort Worth, Texas
Posts: 40,968
chscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond reputechscag has a reputation beyond repute
Mac Specs: 27" iMac i5, 3.2 GHz, iPad 3, iPhone 5c, 3 iPods, Yosemite

chscag is online now
I don't know if this will help or not, but most macros ported from Windows Office 2010 to Mac Office 2011 have to be recompiled. I don't use Excel so I can't help you with your code, however, check out this web site where some of the differences in code are shown.

LINK
QUOTE Thanks

Post Reply New Thread Subscribe


« guest accounts | Time Machine Backup - connection fails »
Thread Tools

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off
Forum Jump

Similar Threads
Thread
Thread Starter
Forum
Replies
Last Post
2006 Macbook Startup Issue? rjt2116 Apple Notebooks 1 01-04-2011 08:47 PM
Mouse click menu issue iwonder2 Apple Desktops 0 12-27-2010 08:04 PM
Continuous Kernel Panic Issue Zilch Apple Notebooks 8 10-04-2010 07:17 AM
Disk Error, then FOLDER WITH QUESTION MARK ISSUE. rohit.dhamija Apple Notebooks 1 09-14-2010 02:52 PM
Issue with Mac Safari browser harryindia OS X - Apps and Games 4 11-21-2006 07:31 PM

All times are GMT -4. The time now is 06:06 PM.

Powered by vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
X

Welcome to Mac-Forums.com

Create your username to jump into the discussion!

New members like you have made this community the ultimate source for your Mac since 2003!


(4 digit year)

Already a member?