VBA Convert string to range

[cameronb]

I am designing a from to create a graph based on data which is pasted into a specific sheet in a workbook. The form has two cells which has the letter reference for the data source for a graph.

In my code, I am attempting to convert the letter stored as a string into a range. The code has an error at line:

Nalastrow = Sheets("Datadrop").Range(" & Na & 65536 ").End(xlUp).Row

Can anyone assist with the correct syntax for converting strings to ranges?

Sub Chartgraph()
Dim Na As String
Dim Ca As String
Dim Nalastrow As Long
Dim Calastrow As Long
Dim Nar As Range
Dim Car As Range
 
Na = Application.WorksheetFunction.Trim(Sheets("Key Stats").Range("B8").Value) 'cell with column reference
Ca = Application.WorksheetFunction.Trim(Sheets("Key Stats").Range("B9").Value) 'cell with column reference
 

Nalastrow = Sheets("Datadrop").Range(" & Na & 65536 ").End(xlUp).Row
Calastrow = Sheets("Datadrop").Range(" & Ca & 65536 ").End(xlUp).Row

Na = Na & 2
Ca = Ca & 2
Set Nar = Sheets("Datadrop").Range(" & Na & : & Na & Nalastrow")
Set Car = Sheets("Datadrop").Range(" & Ca & : & Ca & Calastrow")
[CODE\]

 

[Sektor]

What are Na and Ca?

 

[VoG]

Try

Nalastrow = Sheets("Datadrop").Range(" & Na & ", Rows.Count).End(xlUp).Row

 

[cameronb]

Na and Ca are the declared strings which correspond to the columns which will form data source for the chart.

e.g. Na = "H" - meaning column H
Ca = "J" - meaning column J


For

Nalastrow = Sheets("Datadrop").Range(" & Na & ", Rows.Count).End(xlUp).Row

I get an error showing Nalastrow as o?

 

[VoG]

Sorry, it should be

Nalastrow = Sheets("Datadrop").Range(" & Na & " & Rows.Count).End(xlUp).Row

 

[Sektor]

Nalastrow = Sheets("Datadrop").Cells(Na, Rows.Count).End(xlUp).Row

 

[cameronb]

I am still having a code error with line :

Nalastrow = Sheets("Datadrop").Range(" & Na & " & Rows.Count).End(xlUp).Row

Any suggestions?

Sub Chartgraph()
Dim Na As String
Dim Ca As String
Dim Nalastrow As Long
Dim Calastrow As Long
Dim Nar As Range
Dim Car As Range
 
Na = Application.WorksheetFunction.Trim(Sheets("Key Stats").Range("B8").Value) 'cell with column reference
Ca = Application.WorksheetFunction.Trim(Sheets("Key Stats").Range("B9").Value) 'cell with column reference
 
Nalastrow = Sheets("Datadrop").Range(" & Na & " & Rows.Count).End(xlUp).Row
Calastrow = Sheets("Datadrop").Range(" & Ca & " & Rows.Count).End(xlUp).Row
 

Set Nar = Sheets("Datadrop").Range(" & Na & 2 & : & Na & Nalastrow")
Set Car = Sheets("Datadrop").Range(" & Ca & 2 & : & Ca & Calastrow")

 

[Jonmo1]

This

Nalastrow = Sheets("Datadrop").Cells(Na, Rows.Count).End(xlUp).Row
should be
Nalastrow = Sheets("Datadrop").Cells(Rows.Count, Na).End(xlUp).Row

The syntax for Cells is

Cells(Row#, Column # OR Letter)


Hope that helps.