Code Error: object variable not set

[markeus]

HI

I am having some trouble with some of my code. I am trying to set a variable for a cell's address. The cell is selected from a previous loop which will go through all the values of a sheet and calculate a formula into a selected cell and then stop calculating when the cell below is empty. I then want to reset this cell address variable to the cell above as the current cell's variable would be set for the blank lower cell. Problem is I keep getting an error "object variable or with block variable not set"

this is the code:

[VBA]'Do GREC
Do While cellGR <> ""

If 0.0802 * cellGR - 1.6721 < 0 Then
cellGREC.Value = "0"
cellGREC.NumberFormat = "0.00%"
Else
cellGREC.Value = (0.0802 * cellGR - 1.6721) / 100
cellGREC.NumberFormat = "0.00%"
End If

Set cellGR = cellGR.Offset(1, 0)
Set cellGREC = cellGREC.Offset(1, 0)
Set cellLastDepth = cellFirstDepth.Offset(1, 0)
Loop

End If
'create Chart
Application.ScreenUpdating = False

addrLGREC = cellGREC.Offset(-1, 0).Address
addrLGR = cellGR.Offset(-1, 0).Address
addrLD = cellFirstDepth.Offset(-1, 0).Address

xData = Range(addrFD, addrLD)
yData = Range(addrFGREC, addrLGREC)[/VBA]

I appologize if my tags didn't work, this is my first post. Any help is appreciated!

[markeus]

I have all of my variables set as Ranges and they are set and defined in code written above.

[Aussiebear]

Where is the range addrFD defined?

[Bob Phillips]

Would be better to see the workbook, too many unknowns in your code as shown.

[Paul_Hossler]

1. Based on the small fragment of code it's hard to tell

2.
[VBA]
xData = Range(addrFD, addrLD)
yData = Range(addrFGREC, addrLGREC)
[/VBA]

probably should be

[VBA]
Set xData = Range(addrFD, addrLD)
Set yData = Range(addrFGREC, addrLGREC)
[/VBA]

Paul

[markeus]

I will show the entire code here..
[VBA]
Sub GREC()
Dim cellGR As Range, cellGREC As Range
Dim cellLastGR As Range, cellLastGREC As Range
Dim cellFirstDepth As Range, cellLastDepth As Range
Dim xData As Range, yData As Range
Dim AWS As Worksheet
Dim GraphRange As Range
Dim addrFGR As Range, addrFGREC As Range, addrLGR As Range, addrLGREC As Range, addrFD As Range, addrLD As Range


Start:
'Choose GR Cell and Depth Cell
On Error Resume Next

Application.DisplayAlerts = False

Set cellGR = Application.InputBox(Prompt:="Please choose the cell with the first GR Value", _
Title:="Choose GR Cell", Type:=8)
addrFGR = cellGR.Address

Set cellFirstDepth = Application.InputBox(Prompt:="Please choose the cell with the first Depth Value", _
Title:="Choose Depth Cell", Type:=8)
addrFD = cellFirstDepth.Address

On Error GoTo 0

Application.DisplayAlerts = True

'If no cell selected, exit sub
If cellGR Is Nothing Then

Exit Sub

ElseIf cellFirstDepth Is Nothing Then

Exit Sub
'if an empty cell is selected loop back
ElseIf cellFirstDepth = "" Then

MsgBox "Depth cell is empty.", vbOKOnly, "Error"
GoTo Start

ElseIf cellGR = "" Then

MsgBox "GR cell is empty.", vbOKOnly, "Error"
GoTo Start
End If

GREC:
'Check if Destination Cell for GREC is full
On Error Resume Next

Application.DisplayAlerts = False

Set cellGREC = Application.InputBox(Prompt:="Please choose the cell where you would like the GREC value to go.", _
Title:="Choose GREC Cell", Type:=8)
addrFGREC = cellGREC.Address

MsgBox (addrFGREC)
On Error GoTo 0

Application.DisplayAlerts = True


If cellGREC Is Nothing Then

Exit Sub


ElseIf cellGREC <> "" Then
MsgBox "Cell is full.", vbOKOnly, "Error"
GoTo GREC

Else

'Do GREC
Do While cellGR <> ""

If 0.0802 * cellGR - 1.6721 < 0 Then
cellGREC.Value = "0"
cellGREC.NumberFormat = "0.00%"
Else
cellGREC.Value = (0.0802 * cellGR - 1.6721) / 100
cellGREC.NumberFormat = "0.00%"
End If

Set cellGR = cellGR.Offset(1, 0)
Set cellGREC = cellGREC.Offset(1, 0)
Set cellLastDepth = cellFirstDepth.Offset(1, 0)
Loop

End If
'create Chart
Application.ScreenUpdating = False

addrLGREC = cellGREC.Offset(-1, 0).Address
addrLGR = cellGR.Offset(-1, 0).Address
addrLD = cellFirstDepth.Offset(-1, 0).Address

xData = Range(addrFD, addrLD)
yData = Range(addrFGREC, addrLGREC)

Chart.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=GraphRange
ActiveChart.Location Where:=x1LocationAsNewSheet, Name:="GREC Chart"
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Caption = "Depth"
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "%K2O"

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "GREC"
ActiveChart.SeriesCollection(1).XValues = xData
ActiveChart.SeriesCollection(1).Values = yData

Application.ScreenUpdating = True

End Sub[/VBA]