Free Microsoft Excel 2013 Quick Reference

Convert a string to cell reference in VBA

I have a piece of VBA code like this

Cell A1 has the value G4.

varName = Range("A1").Value
Range("C2").Formula = "='Sheet2'!" & varName

What I'm trying to do is write this formula Sheet2!G4 within cell C2. Essentially, I guess I'm trying to achieve an indirect.

But when i run it, C2's formula appears like this:
'Sheet2'!'G4'

It also shows an error. I'm assuming this is because of G4 appearing in quotes when the formula is written in the cell. How can I remove this? How can I change the string varName to a range type? Or is there any other workaround for this?

---------Cross-posted in Mr Excel here.---------------


Post your answer or comment

comments powered by Disqus
I am trying to convert a string into date type in VBA..lets say
str="200803".
I am using Cdate(str) to convert it into date and it returns 10/10/2449 in this case..

I want the date to be returned in mmm-yy format..like if the string is "200803" ,it should return Mar-08.

Hi,

Using VB .Net please could someone help me with the syntax to convert a
string to number. I want this to turn the computer name into an value so I
can pass it through a function to determine a unique key for the machine.

Hey all,

I have a fairly simple nested IF function judging a couple of ISBLANKs as well as some LEN and RIGHT. The problem is that I'd like to put it in VBA. Can do that easily. Here's the catch: I'd like to make the column references dynamic in case the columns get re-arranged. So if "Title 1" gets moved to column C, I'd like the formula to use column C. Does that make sense? Here's the formula in a regular cell.

=IF(ISBLANK(A3),"",IF(ISBLANK(C3),IF(LEN(A3)=13,RIGHT(A3,11),A3),IF(LEN(A3)=13,RIGHT(A3,11)&"-"&C3,A3&"-"&C3)))

The code is easy, but making it so the column can shift is a bit harder. I was trying to include Match("Title 1",$A$1:$Z$1,0) inside, but that doesn't return letters and it gets too complicated for the macro to record.

Any help?

Thank you so much. I appreciate the help.

Hi!
The dates that I am working with comes in this form: 2006-06
This represents 2006 Jun

The purpose of my program is to obtain the number of days between 2006-06 and today's date.
A part of my program is as follows:
Ex = "2006-06"
InputYr = Mid(Ex, 3, 2) ' To obtain 06, the yr
InputMth = Mid(Ex, 6, 2) 'To obtain 6, the mth

dd1str = InputMth & "/31/" & InputYr ' To obtain a string 06/31/06
As the datediff function requires the data type of dd2 and dd1 to be the same, i converted the string to date using DateValue
dd1 = DateValue(dd1str)
dd2 = Date 'Todays Date

NoOfDays = DateDiff("d", dd2, dd1)

However, i kept encountering error at the line with datevalue.
Would appreciate it if someone can help me with this! thanks!
Dorothy

I was working on a project recently where I had to convert a string into to a 2 dimensional array. I'm not saying this is the best way, but its the way I ended up doing it

When calling the function, just specify the string, the number of columns you want the resulting array to have and the seperator to use for converting the string to a 2 dimensional array. The default seperator is a space character, but could be anything you want. It will calculate the required number of rows.


	VB:
	
 
Option Base 0 
 
Public Function Str_2d(str As String, intCol, Optional Delim As String = " ") As Variant 
     
     'convert a string to a 2 dimensional array - using space as default delimiter.
     
    Dim Num_Rows As Long 
    Dim arrTemp, arrTemp2 
    Dim iCount As Integer, Row_Count As Integer, Col_Count As Integer 
     
    Num_Rows = Application.RoundUp((Len(str) - Len(Replace(str, Delim, "")) + 1) / intCol, 0) 'determine size and shape of
resulting array - number of rows for number columns
    arrTemp = Split(str, Delim): iCount = 0 'icount is the index for arrtemp and that is a zero based array
    Redim arrTemp2(Num_Rows - 1, intCol - 1) 
    For Row_Count = 1 To Num_Rows 
        For Col_Count = 1 To intCol 
            arrTemp2(Row_Count - 1, Col_Count - 1) = Trim(arrTemp(iCount)) 
            iCount = iCount + 1 
            If iCount > UBound(arrTemp) Then Exit For 
        Next 
    Next 
    Str_2d = arrTemp2 
     
End Function 
 
Public Sub driver() 
     
    Dim x 
    ActiveSheet.Cells.Clear 
    x = Str_2d("This is a sweet function for 2 dimensional arrays Ha! Ha", 3) 
     'or
     'x = Str_2d("This is a sweet function^for 2 dimensional arrays^Ha! Ha", 3, "^")
     'or
     'x = Str_2d("This is a sweet,function for 2,dimensional,arrays,Ha! Ha", 1, ",")
     'display the result....
    ActiveSheet.Range("A1").Resize(UBound(x, 1) + 1, UBound(x, 2) + 1) = x 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope someone finds it useful.

Ger

I need to be able to locate a date in a spreadsheet bassed uppon a user input date. I am looking for the week that contains the user's date. However it seems even if I initialize the value as Date, it still reads the user's input as a string.

Is there an easy way make sure the system automaticaly recognizes the user's input as a date, or is there a way to quicly convert the string to a date?

Thanks!

Hi all.

Good news, Google answers most of my questions. Bad news, not this one.

I'm trying to use vlookup in vba to look at a specific source cell.

If the month is Jan, the vlookup formula goes into column L, for Feburary it goes into column T, March goes into AB - but I always want the source column to be A.

I tried this, but it didn't work...

ActiveCell.FormulaR1C1 = "=VLOOKUP($A1,M1:Z53,2,FALSE)"

Anyone know what I need to do?

Thanks

Colin

Morning everyone,

I think someone has been fiddling with my Excel settings, because today when I record a macro, the cell references are coming out in R1C1 style instead of the normal A1 style that they did last week.
I've checked in Tools, Options, General, and the R1C1 reference style is NOT checked.
Is there somewhere else this can get changed?

Thanks

Dave

Hi all,

I'm wondering how if possible to convert a string to a Boolean
For instance, normally someone would use:
Dim continue As Boolean
continue = True

If continue = True Then MsgBox "Boolean variables are cool"
However, I'd like to say something like the following.

Dim tocontinue1 As Boolean
Dim tocontinue2 As Boolean

'choose is only a string that I want to change to represent the tocontinue1 Boolean

choose = "tocontinue1"

 If Z = 1 Then choose = True
If tocontinue1 = True  or Then MsgBox "Boolean variables are cool"
Any Help is welcome.

Thanks,

BDB

Okay, how do you convert .89 to just 89 in excel? Or, how would you convert a 89% to just 89 in excel?

Hi All,

I have a problem to convert a string to date. The format of data follows:

Jan 4 2006

Desired format

1/4/2004

I have tried function like date value but getting error #VALUE error. Please help me with this.

With sincere regards,
Upananda

This is my first time and I am new to VBA so please bear with me.

I have a worksheet with 1500 records. I already have a code which asks me to input a search string, goes through each row of the workshet and if Column C matches the search string, copies the entire row to a second sheet on the row next to the last row. The macro also counts the number of instances found so at the end of the search, we know that there were for example 28 records matching the string i.e. 28 new rows in the second sheet. The new set of records (28 rows in this case) are separated from the previous records by a gap of two rows.

I have also written code to name the new 28 cells in F column to lets say MyRange. Then the macro goes to the 29th cell in F row and needs to put in a formula to sum the above 28 rows. I can't use xlUp because there may be blank cells in between.

I had originally used
ActiveCell.FormulaR1C1 = "=sum(MyRange)"
 but at the end of the
macro I need to delete name reference so that the code can run next time for a different search string without any
problem.

My problem is how do I convert MyRange to the actual cell reference from what we know which is:
(i) The cells to be summed are always in F column.
(ii) The Active Cell is already one row below the range to be summed.
(iii) The number of rows is stored in a variable called Counter

Basically what I need is Sum(R-[Counter]C[0]:R-[1]C[0])

I know this must be very simple for you veterans but I am stumped. Thank you for your help.

How do I convert a date to a string in VBA?

	VB:
	
 
 
 'a is being read from a cell in excel that is a date
a = "2/3/2006" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So how do I get the variable a to act like a string. When I attempt to add to another string in excel I receive an error.

Thanks in advance.

Hi all!

I'm hoping one of you VBA wizards can help me!

I would like to be able to select a range of cells in a column that will contain a text string I wish to convert the string to a hyperlink. Some of these cells will be populated; some not. I'd like the macro I'm requesting to ignore the empty cells and continue on down the selected column range until finished with the selection.

Here is an example of the format for the string:
text.to.display.ppt#http://someplace%20on%20a%20server%2...hereelse%2Eppt

I would like the first part of the string up to, but not including the '#' sign to display in the cell. I would like everthing else after the '#' sign to become the embedded hyperlink's destination. In other words, the '#' sign delimiter needs to fall off the face of the earth! ;-}

So, you would only see 'text.to.display.ppt' in the cell underlined as a hyperlink, but clicking on the displayed text would take you to 'http://someplace%20on%20a%20server%2Ecom%2Fsomewhereelse%2Eppt'.

I would like the final result to completely replace the contents of the cell in which the text string resides.

I hope this is clear. I suspect this is a very short macro, but I lack the necessary skills to program the code.

Any takers?!

Thanks for any and all replies!
-dougbert

Hi all,

I'm not sure if this is possible, but is there any way I can convert a text string (let's say "N28") into a cell reference in a formula?

In other words, "N28" is a text string a cell, but I want to create a formula using that cell as a reference.

Basically I want another cell's value to be "=N28", but the string is dynamic and needs to be reflected in the other cell.

Not sure if that makes much sense?

Thanks in advance!

Hoping that someone can offer some advice on this....

I'm creating a complex excel sheets with lots of relative, mixed, and
absolute references. Once I'm done, I'd like to copy a block within the
sheet to create a new block. The trouble is that all the absolute
references within the block are pointing to reference within the old block.
I can fix them manually, but I want to copy the block several times, and
it's a lot of work to change each one

What I'd like to to do is, once things are as I want them, convert all of
the Absolute references in the completed block to relative so that when I
past a new block, the addressing will be correct. One trick I've found is
that I can convert cells formulas from Absolute to relative one by one using
the F4 key, but that's not ideal either (although, it's easier than editing
each cell). Anyone know if there is a way to convert a block of cells to
relative mode in one shot? Thanks for any advice.

Hi,

Background Information:
I am creating a DASHBOARD sheet to be at the beginning of my workbook with multiple sheets.

In DASHBOARD I have created a chart that contains client analysis formulas that reference to the appropriate client sheet name in the same workbook by reference to Cell C8. To do this, in each statistics formula I have used the Indirect reference: =INDIRECT("'" & C8 &"'!G3")

My next step is to make a dynamic button that also uses Cell C8 as the sheet name to jump to. AKA: I want the "Go" button to jump the user exactly to the client sheet which is named in cell C8.

I have used this simple VBA to use a button to jump to a sheet name:
Private Sub Client_Click()

Sheets("Client-Template").Select

End Sub
I need help to make the sheet reference dynamically linked to the contents of Cell C8 which will have the exact worksheet name of any of the clients:
The following is my failed attempt to create a dynamic variable that depended on Cell C8:
Private Sub Client_Click()
Client as String

Client = Range(C8)
Sheets("Client").Select

End Sub
Thank you for any and all of your help!

Good Morning. I found a post that provided several macros to force cell
references in a range to Absolute and tried it. Two problems - I can't find
the post to respond to and when I applied it via VBA it changed the last two
columns correctly, but the first column no shows the formula and I can't seem
to change it back to the values as so:
NAME AVG HDCP
='[Blues-27-Apr-06.xls]Sub_Scores'!$B$5 32.0 1

The code used was:
Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Any help will be appreciated...

I am trying to store an expression that includes variable names and strings ("= ClientName & " " & VerbRises & " " & "to meet " & WorkerName & ". "") and then present the evaluated expression (with the variables replaced with their string values). In other words I want to convert the expression string to a string that has been evaluated.

When I put quote marks around the expression (as in cell D2), then the variables dont get replaced with their values (see cell D12). When I dont use the quote marks (beginning and ending), then I get an error message.

When I dont use the outer quote marks and then put the expression in the VBA code (macro), then things work okay, but I want to store the expression in a table cell, not in the VBA code.

Any tips would be welcomed!

I have named the column headings in a worksheet "colMillMonth". Say the value in the first cell is "xyz - Mar". I want to retrieve "Mar" and convert it into its full month name (March). I tried the following, but it remains a string. Any suggestions?

	VB:
	
 myMonth() 
    Dim strMonth As Month, dMonth As Date 
     
    strMonth = Sheet32.Range("colMillMonth").Cells(1).Value 
    dMonth = Format(Right(strMonth, 3), "mmmm") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi. How can any string valid for the name of a workbook be converted to a string correctly recognized by Application.Run? Sometimes a string is not really recognized as a string. See for example:


	VB:
	
 
my_book_macro = "Book No.1!my_macro" 
Application.Run my_book_macro 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code gives a runtime error 1004, because the Blank and the Dot are "not allowed" in the string here. If the code is changed to the following, it runs fine (sure both times the correct workbook is open when the code runs).


	VB:
	
my_book_macro = "Book_No1!my_macro" 
Application.Run my_book_macro 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any suggestion to solve this.
Dasha.

Hi!
I'm frustrated.
I get that there are many ways to reference cell addresses(A1, RC, offset from an active cell, etc.). But when to use which
Putting one of these into a VBA formula I don't get. I'm almost ebarassed to even broach the subject to such knowledgable people

Okay, after looking through an awful lot of posts, what does the "!" mean in the following: !R1C1:R44C5 Is it an absolute vs relative indicator?

I'm working on a SUM(range) formula but although Excel macro words can be used in VBA, I can't find an explaination in VBA Help. Its index stops at "Submit". The VBA help lists it as a worksheet function but not how to use in a VBA module.

I NEED TO READ - Please point me in some direction (online hopefully).

I note the "Cells" property and it has the column, a comma, and the row. That would seem ideal to use (a variable for the row))

This is what I'm attempting in a very crude manner
Qty=sum(cell($c, $row) to (cell($c, row variable)))

I've dim'd the variables. Running it, the "sum" is highlighted and the error is "Sub or Fuction not defined"

While I'd like the answer, I'd also like to be pointed to a source for putting things together.

Hope my rambling has at least lead to a and that I have not taken up too much forum space and your time. Please don't kick me out. I'm trying! At 58 some of the brain functions may be gone.
John

I'd like to be able to refer to the 'name' of a cell, rather than it's location, in VBA macro code. This way, if I move the cell, the macro will know where to look for the value.

Can anyone help?

Thanks.


Hi.
I have a series of formula which use the same condition.
Eg:
C1 contains the string of the testing condition, [eg IF(AND($D1=$E1, $F1=0),
"", ]
G1-Z1 make reference to condition in A1, [eg ={call the condition in C1}, {some
function to do}]

C2 contains the string of testing condition, [eg IF(AND($D2=$E2, $F2=0), "", ]
G2-Z2 make reference to condition in A1, [eg ={call the condition in C2}, {some
function to do}]

** Problem **
1)
How can I call the testing condition (to be used by other cells)?
Note: I need to do so because I can update the testing condition once when
there're changes.
Otherwise I need to update each of them which is tedious.

2)
For cell reference in the string, it will not be updated when I drag the formula
along the table.
Does anyone know how to slove this problem?

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

HOD`**. pB, Ѫ̤[祿!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.


No luck finding an answer? You could always try Google.