convert Variant to string

[amitbern]

I'm using vlookup in vba , but the vlookup result must be in variant type

lookup_result = Application.VLookup(cell, range, col, False)

how can i convert lookup_result from variant type to string?

 

[Nimrod]

Public Sub Demo()
Dim StringVal As String
Dim VariantVal As Variant

VariantVal = "test"

' CONVERT TO STRING <<<<<<<< EXAMPLE HERE <<<<<<<
StringVal = CStr(VariantVal)

End Sub

Type Conversion Functions ( SOURCE VBA help file)


Each function coerces an expression to a specific data type.

Syntax

CBool(expression)

CByte(expression)

CCur(expression)

CDate(expression)

CDbl(expression)

CDec(expression)

CInt(expression)

CLng(expression)

CSng(expression)

CStr(expression)

CVar(expression)

 

[iknowu99]

how about when variant is a range?

like

VariantVal  = Worksheets("Sheets1").Range("C2:C" & Sheets("Sheets1").Range("c1000000").End(xlUp).Row).Value

StringVal = CStr(VariantVal)
is not working for me?

i would like to create a text file with the column C, i know i can make a loop and create the text string with vbCrLf and then export with

'    For c = 2 To Range("C1000000").End(xlUp).Row
'    RtnPage = RtnPage & Cells(c, 3) & vbCrLf
'    Next c
Set fs = CreateObject("Scripting.FileSystemObject")
Set ah = fs.CreateTextFile("C:\all.txt", True)
ah.Writeline (RtnPage)
ah.Close

the loop part is taking forever. help!

 

[Jon von der Heyden]

See here: http://www.mrexcel.com/forum/showthread.php?p=2222663&posted=1#post2222663

 

[Rick Rothstein]

'    For c = 2 To Range("C1000000").End(xlUp).Row
'    RtnPage = RtnPage & Cells(c, 3) & vbCrLf
'    Next c

the loop part is taking forever. help!

You should be able to replace that loop with the following single line of code and it should be much faster...

[table="width: 500"]
[tr]
	[td]RtnPage = Join(Application.Transpose(Range("C2", Cells(Rows.Count, "C").End(xlUp))), vbNewLine)[/td]
[/tr]
[/table]

Edit Note: Just realized I answered an old thread because someone made a strange reply to it today, however I decided to leave my response up as it might prove useful to someone who stumbles across this thread in the future.

 

[shg]

I don't think that will work if there are more than 64K rows.

 

[Rick Rothstein]

I don't think that will work if there are more than 64K rows.

You are right, of course, I had forgotten about that limit (always limits in Excel... very annoying). Actually, the code line works (no errors), but it only grabs the first 65535 rows of data and ignores any remaining rows.

 

[Jon von der Heyden]

Edit Note: Just realized I answered an old thread because someone made a strange reply to it today, however I decided to leave my response up as it might prove useful to someone who stumbles across this thread in the future.

I removed the strange reply (just thought I'd point it out otherwise someone may think you're being a bit odd!).