Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting " into a String
I have been trying to write a small help to get an indirect formula
into Sheet2. Problem is that the indirect formula needs to have the " in it: =indirect(Sheet1!"A1"). what I tried to do is this Dim Cell As String Dim Cell2 As String Dim Teller As Integer Dim Letterteller As Integer For Letterteller = 1 To 5 If Letterteller = 1 Then Letter = "A" If Letterteller = 2 Then Letter = "B" If Letterteller = 3 Then Letter = "C" If Letterteller = 4 Then Letter = "D" If Letterteller = 5 Then Letter = "E" For Teller = 1 To 100 Cell = Letter & Teller Cell2 = """ & Cell & """ ActiveCell.Formula = "=indirect(Sheet1!" & Cell2 & ")" Next Next Something has to be wrong with the Definition of Cell2 ( Cell2 = """ & Cell & """), but I can't figure out how to get around it. And I definitly don't want to manually change 500 cells myself. :-( Anyone? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting " into a String
On 27 mrt, 15:43, "Dan Jansen" wrote:
I have been trying to write a small help to get an indirect formula into Sheet2. Problem is that the indirect formula needs to have the " in it: =indirect(Sheet1!"A1"). what I tried to do is this Dim Cell As String Dim Cell2 As String Dim Teller As Integer Dim Letterteller As Integer For Letterteller = 1 To 5 If Letterteller = 1 Then Letter = "A" If Letterteller = 2 Then Letter = "B" If Letterteller = 3 Then Letter = "C" If Letterteller = 4 Then Letter = "D" If Letterteller = 5 Then Letter = "E" For Teller = 1 To 100 Cell = Letter & Teller Cell2 = """ & Cell & """ ActiveCell.Formula = "=indirect(Sheet1!" & Cell2 & ")" Next Next Something has to be wrong with the Definition of Cell2 ( Cell2 = """ & Cell & """), but I can't figure out how to get around it. And I definitly don't want to manually change 500 cells myself. :-( Anyone? Sorry, found it already: use Chr(34) instead of """ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting " into a String
Or use """"
In article . com, "Dan Jansen" wrote: Sorry, found it already: use Chr(34) instead of """ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting " into a String
For Letterteller = 1 To 5
If Letterteller = 1 Then Letter = "A" If Letterteller = 2 Then Letter = "B" I see you have an answer. Just throwing out an idea for a part of your code... For LetterTeller = 1 To 5 Letter = Chr(LetterTeller + 64) 'etc Next -- HTH :) Dana DeLouis Windows XP & Office 2007 "Dan Jansen" wrote in message oups.com... I have been trying to write a small help to get an indirect formula into Sheet2. Problem is that the indirect formula needs to have the " in it: =indirect(Sheet1!"A1"). what I tried to do is this Dim Cell As String Dim Cell2 As String Dim Teller As Integer Dim Letterteller As Integer For Letterteller = 1 To 5 If Letterteller = 1 Then Letter = "A" If Letterteller = 2 Then Letter = "B" If Letterteller = 3 Then Letter = "C" If Letterteller = 4 Then Letter = "D" If Letterteller = 5 Then Letter = "E" For Teller = 1 To 100 Cell = Letter & Teller Cell2 = """ & Cell & """ ActiveCell.Formula = "=indirect(Sheet1!" & Cell2 & ")" Next Next Something has to be wrong with the Definition of Cell2 ( Cell2 = """ & Cell & """), but I can't figure out how to get around it. And I definitly don't want to manually change 500 cells myself. :-( Anyone? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting " into a String
try this
Sub asd() Dim Cell As String Dim Cell2 As String Dim Teller As Integer Dim Letterteller As Integer For Letterteller = 1 To 5 If Letterteller = 1 Then Letter = "A" If Letterteller = 2 Then Letter = "B" If Letterteller = 3 Then Letter = "C" If Letterteller = 4 Then Letter = "D" If Letterteller = 5 Then Letter = "E" For Teller = 1 To 100 Cell = Letter & Teller Cell2 = "" & Cell & "" Cell2 = "=indirect(Sheet1!" & Cell2 & ")" ActiveCell.Formula = Cell2 Next Next "Dan Jansen" wrote: I have been trying to write a small help to get an indirect formula into Sheet2. Problem is that the indirect formula needs to have the " in it: =indirect(Sheet1!"A1"). what I tried to do is this Dim Cell As String Dim Cell2 As String Dim Teller As Integer Dim Letterteller As Integer For Letterteller = 1 To 5 If Letterteller = 1 Then Letter = "A" If Letterteller = 2 Then Letter = "B" If Letterteller = 3 Then Letter = "C" If Letterteller = 4 Then Letter = "D" If Letterteller = 5 Then Letter = "E" For Teller = 1 To 100 Cell = Letter & Teller Cell2 = """ & Cell & """ ActiveCell.Formula = "=indirect(Sheet1!" & Cell2 & ")" Next Next Something has to be wrong with the Definition of Cell2 ( Cell2 = """ & Cell & """), but I can't figure out how to get around it. And I definitly don't want to manually change 500 cells myself. :-( Anyone? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting " into a String
I assume you want to end up with =Indirect("Sheet1!A1")
as an example. so use Cell2 = Cell & """" ActiveCell.Formula = "=indirect(""Sheet1!" & Cell2 & ")" to demo from the immediate window: cell = "B9" Cell2 = Cell & """" ? cell2 B9" ? "=indirect(""Sheet1!" & Cell2 & ")" =indirect("Sheet1!B9") -- Regards, Tom Ogilvy "Dan Jansen" wrote: I have been trying to write a small help to get an indirect formula into Sheet2. Problem is that the indirect formula needs to have the " in it: =indirect(Sheet1!"A1"). what I tried to do is this Dim Cell As String Dim Cell2 As String Dim Teller As Integer Dim Letterteller As Integer For Letterteller = 1 To 5 If Letterteller = 1 Then Letter = "A" If Letterteller = 2 Then Letter = "B" If Letterteller = 3 Then Letter = "C" If Letterteller = 4 Then Letter = "D" If Letterteller = 5 Then Letter = "E" For Teller = 1 To 100 Cell = Letter & Teller Cell2 = """ & Cell & """ ActiveCell.Formula = "=indirect(Sheet1!" & Cell2 & ")" Next Next Something has to be wrong with the Definition of Cell2 ( Cell2 = """ & Cell & """), but I can't figure out how to get around it. And I definitly don't want to manually change 500 cells myself. :-( Anyone? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
Converting string "061123" into a date "23/11/06" | Excel Programming | |||
Making "examp le" become "examp_le" in a string | Excel Programming |