MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Passing a worksheet as an argument while calling the procedure

  Asked By: Pamela    Date: Mar 20    Category: MS Office    Views: 1488

I am having type mismatch error while the code below. Can someone
help me on this?

Sub iterate()
Dim shtFrom, shtTo, shtStart As Worksheet
Set shtStart = Sheets("Class Template")

Dim shtName1, shtName2 As String
Dim iterNum
iterNum = shtStart.Cells(7, 1).Value
MsgBox "Iteration" & iterNum
shtName1 = "Iterate" & iterNum
shtName2 = "Iterate" & iterNum - 1
If iterNum = 1 Then
Set shtFrom = Sheets("Original")
Set shtFrom = Sheets(shtName2)
End If
'***###*** code BELOW is to set initial values for i and j column
Call populateIJ(5, 1, shtFrom)'###***### I am getting compilation
error here
'****there are other code here too. But it is not relevant to
'the problem I am facing
End Sub

Sub populateIJ(x As Integer, y As Integer, z As Worksheet)
Dim rowIJ
Dim shtFrom As Worksheet
Set shtFrom = Sheets(z)
rowIJ = 34 'column J =12 column I=13
Do While rowIJ < 39
If shtFrom.Cells(rowIJ, 21) = x Then
shtFrom.Cells(rowIJ, 12) = 1
shtFrom.Cells(rowIJ, 12) = 0
End If
If shtFrom.Cells(rowIJ, 21) = y Then
shtFrom.Cells(rowIJ, 13) = -1
shtFrom.Cells(rowIJ, 13) = 0
End If
rowIJ = rowIJ + 1
End Sub



1 Answer Found

Answer #1    Answered By: Xander Thompson     Answered On: Mar 20

The problem  arises because of this line:
Dim shtFrom, shtTo, shtStart As Worksheet
which declares ONLY shtStart as a worksheet. You need to declare type
Dim shtFrom as Worksheet, shtTo as Worksheet, shtStart as worksheet

In the populate macro you will also get a type  mismatch in the line:
Set shtFrom = Sheets(z)
which should probably read:
Set shtFrom = z
since you've already defined z as a worksheet.

Finally, although you'll probably get away with it, I'd steer away
from using the same object name (shtFrom) in the two macros.