MrExcel.com - Your One Stop for Excel Tips & Solutions

Concatenating two cell addresses

Posted by Edward C. on April 10, 2001 6:41 AM

Hi,
I am writting a routine to generate some plots, and as part of that, I need to be able to first automatically identify the min and max value to plot over the x-axis.
I have written a routine using Find that tells me based on one column of data where the plot should begin. However, this column of data is in column BR503, and I need to grab the text from column A503 in order to us is as the min value for my chart's x-axis. I have pasted the code below that I am using. I have the first part which find's the row# in BR where I want to start plotting. Also, I have the next two lines which were my attempt to take the address found in the above Find statement and concatenate that with column A to get the text out of cell A503.
I know what I did wrong, but I don't know how to fix it. Before I can concatenate BR503 and A, I have to remove the BR so I get A503 and not ABR503.
If anyone knows how to do this, or knows of a better way to do what I need. Please let me know. Any help would be greatly appreciated.
Thanks
Edward

Set s100F = Worksheet("Veh1_NY_20010321_1957").Columns("BR"). _
Find(what:="100")
txtStep3min.Text = Range("A" & s100F.Address).Text
txtStep3min.Text = Selection.Text

Posted by Stephane Parent on April 10, 2001 9:02 AM

Hi Edward,
you could probably use a right(s100f,3) function but I guess the easiest way would be to use row instead of address...
txtStep3min.Text = Range("A" & s100F.row).Text

I hope it helps

Stephane Parent

Posted by Edward C. on April 10, 2001 11:10 AM

Thanks,
That worked. I am now having another problem. I used the technique you gave to create the Range varibles used below in the create plot statement but it gives me an error message "application-defined or object-defined error"
When I check the address it looks right,
IE: ARange.Address = $A$503:$A$2353.
I tried the statement below both with and without " "'s around it. Could you tell me what I am doing wrong?
Thanks
Edward

ActiveChart.SetSourceData Source:=Sheets("Veh1_NY_20010321_1957").Range( _
"ARange.Address, HRange.Address, AVRange.Address, CHRange.Address"), PlotBy:=xlColumns

Posted by Stephane Parent on April 10, 2001 11:48 AM

"ARange.Address, HRange.Address, AVRange.Address, CHRange.Address"), PlotBy:=xlColumns

Hi Ed,

I'm not very familiar with plots, specially in macros.
But try this:

dim fullrange as range
fullrange=union(Range(ARange.Address), range(HRange.Address), range(AVRange.Address), range(CHRange.Address))
ActiveChart.SetSourceData Source:=Sheets("Veh1_NY_20010321_1957").fullrange, PlotBy:=xlColumns

I haven't try it. If It does not work, I would suggest you to start a new trend with your new problem.
Good luck

Stephane Parent