Issue with variable type?

Cummins

Board Regular
Joined
Jul 26, 2011
Messages
58
When this step is reached, "Data1 = Str(F1.Cells(x, 1))" I get the error "Type mismatch." It only happens when the input data changes from a string of numbers stored as text "6666666" to "wafer test 112608"
Is there a better declaration for Data1?



Public Ary1() As String
Dim Data1 As String
Public NumBtchs As Integer
Public ExtractMethod


Sub PullAllBatchData()


ExtractMethod = 1

Workbook_Open

End Sub


Public Sub PullManualBatchData()

'Need to define Ary1 as a public array as it will be used in another Module


'ExtractMethod will define in other modules how to handle the data extraction
ExtractMethod = 2
'Defines F1 as the active sheet and activates it.
Set F1 = ActiveWorkbook.ActiveSheet
F1.Activate

'Need to define what the last row in Column A
lastrow1 = F1.Range("A" & Rows.Count).End(xlUp).Row

ReDim Ary1(lastrow1, 1)

NumBtchs = 0
For x = 1 To lastrow1
Data1 = Str(F1.Cells(x, 1))
F1.Cells(x, 1).Activate 'Cell needs to be activated for next line
If IsEmpty(ActiveCell.Value) Then Data1 = ""
If Data1 <> "" Then NumBtchs = NumBtchs + 1: Ary1(NumBtchs, 1) = Data1
Next

Workbook_Open



End Sub
 

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
Try changing it to:

Data1 = Str(F1.Cells(x, 1).Value)

Otherwise you're referring to a cell object (range) and not a string value.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,940
Str only works with numeric values. CStr should do the job.

HTH
DK
 

AMAS

Active Member
Joined
Apr 11, 2010
Messages
472
Just on a side note, if the variable type changes then you may want to declare it as a variant.

Hope this helps.

AMAS
 

Cummins

Board Regular
Joined
Jul 26, 2011
Messages
58
Thanks guys. You caught me just before I jumped off my stool. The Excel version I was using is 2003...but the company is in the process of moving to 2010.
 

Forum statistics

Threads
1,082,548
Messages
5,366,227
Members
400,880
Latest member
dwb

Some videos you may like

This Week's Hot Topics

Top