Getting " into a String

[Dan Jansen]

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?

 

[Dan Jansen]

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 """

 

[JE McGimpsey]

Or use """"

 

[Dana DeLouis]

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

 

[Guest]

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

 

[Guest]

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")