Correct structure
 

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

Correct structure
 

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

Andrew wrote:

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

--

Dave Peterson

Correct structure
 

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")


"Dave Peterson" wrote:

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

Andrew wrote:

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

--

Dave Peterson

Correct structure
 

Yep.

Thanks for the correction. (Copy and paste is bad...very bad!)

JMB wrote:

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")

"Dave Peterson" wrote:

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

Andrew wrote:

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

--

Dave Peterson


--

Dave Peterson

Correct structure
 

Hi all,

I'm now trying to get the variable from another sheet. I am getting an
'Object required' error on this line. (Cell B8 on the 'Criteria' sheet
contains the word 'Data')

Set The_Sheet = "Sheets(""" & Sheets("Criteria").Range("B8").Text & """)"



--
Andrew


"Dave Peterson" wrote:

Yep.

Thanks for the correction. (Copy and paste is bad...very bad!)

JMB wrote:

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")

"Dave Peterson" wrote:

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

Andrew wrote:

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

--

Dave Peterson


--

Dave Peterson

Correct structure
 

try
Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)

"Andrew" wrote:

Hi all,

I'm now trying to get the variable from another sheet. I am getting an
'Object required' error on this line. (Cell B8 on the 'Criteria' sheet
contains the word 'Data')

Set The_Sheet = "Sheets(""" & Sheets("Criteria").Range("B8").Text & """)"



--
Andrew


"Dave Peterson" wrote:

Yep.

Thanks for the correction. (Copy and paste is bad...very bad!)

JMB wrote:

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")

"Dave Peterson" wrote:

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

Andrew wrote:

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

--

Dave Peterson


--

Dave Peterson

Correct structure
 

Hi,

Thanks for your last reply. Setting 'The_Sheet' works but setting
'The_Range1' returns 'Subscript out of range' error.

Dim The_Sheet As Worksheet
Dim The_Range1 As Range
Dim The_Range2 As Range

Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)
Set The_Range1 = Sheets(Sheets("Criteria").Range("B9").Text)


Sheets("Criteria").Range("B8").Text contains 'A1' and
Sheets("Criteria").Range("B9").Text contains 'A2'


Can you then help me replace Sheets("Data").Range("A1").Offset(0,
col).Value = ........' with the variables The_Sheet and The_Range1


--
Andrew


"JMB" wrote:

try
Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)

"Andrew" wrote:

Hi all,

I'm now trying to get the variable from another sheet. I am getting an
'Object required' error on this line. (Cell B8 on the 'Criteria' sheet
contains the word 'Data')

Set The_Sheet = "Sheets(""" & Sheets("Criteria").Range("B8").Text & """)"



--
Andrew


"Dave Peterson" wrote:

Yep.

Thanks for the correction. (Copy and paste is bad...very bad!)

JMB wrote:

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")

"Dave Peterson" wrote:

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

Andrew wrote:

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

--

Dave Peterson


--

Dave Peterson

Correct structure
 

I don't understand. Shouldn't one of those ranges contain a worksheet name?

Set The_Range1 = sheets("Data").range(sheets("Criteria").range("B8" ).value)

====
If b8 on criteria is equal to Data and if b9 on criteria is equal to A1, then

set The_Sheet = sheets(sheets("criteria").range("B8").value)
set The_Range1 = the_sheet.range(sheets("criteria").range("B9").val ue)

The_Sheet will point at the Data worksheet.
The_Range1 will point at A1 of the Data worksheet.


Andrew wrote:

Hi,

Thanks for your last reply. Setting 'The_Sheet' works but setting
'The_Range1' returns 'Subscript out of range' error.

Dim The_Sheet As Worksheet
Dim The_Range1 As Range
Dim The_Range2 As Range

Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)
Set The_Range1 = Sheets(Sheets("Criteria").Range("B9").Text)

Sheets("Criteria").Range("B8").Text contains 'A1' and
Sheets("Criteria").Range("B9").Text contains 'A2'

Can you then help me replace Sheets("Data").Range("A1").Offset(0,
col).Value = ........' with the variables The_Sheet and The_Range1

--
Andrew

"JMB" wrote:

try
Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)

"Andrew" wrote:

Hi all,

I'm now trying to get the variable from another sheet. I am getting an
'Object required' error on this line. (Cell B8 on the 'Criteria' sheet
contains the word 'Data')

Set The_Sheet = "Sheets(""" & Sheets("Criteria").Range("B8").Text & """)"



--
Andrew


"Dave Peterson" wrote:

Yep.

Thanks for the correction. (Copy and paste is bad...very bad!)

JMB wrote:

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")

"Dave Peterson" wrote:

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

Andrew wrote:

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Correct structure
 

to address your last question - assuming The_Sheet points to "Data" and
The_Range1 points to cell A1 on the "Data" worksheet, you could use

The_Range1.Offset(0, col).Value = ???

No need to qualify with The_Sheet for this instruction as your range
variable is set up to point at "Data" cell A1 (I think - I share Dave's
confusion on what data is in cell B8 and B9).

Since B9 appears to contain a range reference (let's say "A2"), your
original code becomes:

Set The_Range1 = Sheets("A2")

I'm sure you do not have a worksheet named "A2", so you get subscript out of
range error.



"Andrew" wrote:

Hi,

Thanks for your last reply. Setting 'The_Sheet' works but setting
'The_Range1' returns 'Subscript out of range' error.

Dim The_Sheet As Worksheet
Dim The_Range1 As Range
Dim The_Range2 As Range

Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)
Set The_Range1 = Sheets(Sheets("Criteria").Range("B9").Text)


Sheets("Criteria").Range("B8").Text contains 'A1' and
Sheets("Criteria").Range("B9").Text contains 'A2'


Can you then help me replace Sheets("Data").Range("A1").Offset(0,
col).Value = ........' with the variables The_Sheet and The_Range1


--
Andrew


"JMB" wrote:

try
Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)

"Andrew" wrote:

Hi all,

I'm now trying to get the variable from another sheet. I am getting an
'Object required' error on this line. (Cell B8 on the 'Criteria' sheet
contains the word 'Data')

Set The_Sheet = "Sheets(""" & Sheets("Criteria").Range("B8").Text & """)"



--
Andrew


"Dave Peterson" wrote:

Yep.

Thanks for the correction. (Copy and paste is bad...very bad!)

JMB wrote:

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")

"Dave Peterson" wrote:

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

Andrew wrote:

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

--
Andrew

--

Dave Peterson


--

Dave Peterson