convert Variant to string

amitbern

New Member
Joined
Jun 7, 2005
Messages
19
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Code:
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

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
how about when variant is a range?

like

Code:
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

Code:
'    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!
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Code:
'    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...
Code:
[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.
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,825
Office Version
  1. 2010
Platform
  1. Windows
I don't think that will work if there are more than 64K rows.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,868
Office Version
  1. 365
Platform
  1. Windows
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!).
 

Forum statistics

Threads
1,148,173
Messages
5,745,179
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top