0

I have a created a sub to check a value of a column and clear contents of a particular range. But when selecting the value, I continuously get type mismatch error. I have tried the following four methods too. The sheet is refreshed from a sql server source and the datatype of it is string. I changed the data in the source to be a int but of now use. Any help will be highly appreciated.

Sub abc()
    Dim var1 As String
    **var1 = Sheets(Sheet1).Cells(1, "A").Value
    var1 = Sheets(Sheet1).Cells(1, "A").Text
    var1 = Sheets(Sheet1).Range("A1").Value
    var1 = Sheets(Sheet1).Range("A1").Text**
End Sub
Subodh Tiwari sktneer
  • 9,906
  • 2
  • 18
  • 22
  • `Sheets("Sheet1")` assuming Sheet1 is its name. – SJR May 18 '17 at 11:29
  • yes.. you are right. Any other thing that I'm missing in the code? –  May 18 '17 at 11:59
  • According to here http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 you should probably be using `Value2` instead of `Value` or `Text` (and definitely not the latter). I have to confess to never using it but perhaps should mend my ways. – SJR May 18 '17 at 12:06
  • tried that too. still the same error. It's really very strange! –  May 18 '17 at 12:13
  • Not sure what is the problem - which line errors?. You've posted four versions of basically the same line but your question refers to other things so you need to post all the code. – SJR May 18 '17 at 12:26
  • Note also: `Sheets("Sheet1")` is redundant, where simply `Sheet1` would suffic -- using the sheet's `CodeName` will allow the code to run even if users change the name or index of the sheet, and also avoids the possible and dreaded 1004 error of unqualified range references. – David Zemens May 18 '17 at 12:58
  • oops. To be more specific. I want to read the value of column A1 to a variable. So, I have tried using the 4 methods to read the cell value. None of them work. I've included them to show the methods I have tried. var1 = Sheets(Sheet1).Cells(1, "A").Value var1 = Sheets(Sheet1).Cells(1, "A").Text var1 = Sheets(Sheet1).Range("A1").Value var1 = Sheets(Sheet1).Range("A1").Text –  May 18 '17 at 13:04

2 Answers2

0

Try changing var1 type from string to Range or variant.

Vipul Karkar
  • 327
  • 1
  • 3
  • 11
0

Thanks for all the comments provided. Fixed the issue. Added quotes around Changed var1 = Sheets(Sheet1).Range("A1").Value