VBA to create a chart when data can vary in column position and rows

Hi everyone.

I'm attempting to create a macro/VBA to draw some scatterplots, and I need your help!

What I want to do is create a code to automatically look for a column by name, since column order can vary depending what data is exported, and select that column and another column, and create a scatterplot from that data on a new worksheet. My problem is that I don't know how to select column by name as opposed to just column letter. Also, the data can vary in length, so I need to figure out how to select the last row.

I tried Record Macro to get the basic code for creating a scatterplot, but when I tried to re-run it, it came back with an error. Also, it doesn't include selecting columns by name, nor selecting data down to the last row which will vary.

I've attached a worksheet with sample data on sheet1 and desired scatterplots on sheet2.

Any and all help is greatly appreciated!

Attachment 256572

Please Login or Register  to view this content.

chart macro.xlsm

Thanks Andy!

I apologize for being some new to VBA, but when you say "change the x and y names to use", what x and y are you referring to? NameX, NameY? rngDataX, rngDataY?

Thanks again for your help!

Excellent! Thank you very much, this is perfect!!

One more question, I've been trying to figure this out but no luck so far: How can I add something right at the beginning that looks for certain header names, and if not found, displays a message box saying "xxx not found"

I'd like to add something like this because currently if a field is not found a runtime error comes up, which would be confusing to the person using this VBA.

Andy - thanks again for all your help. You have been a tremendous asset, and I thank you for that. I have one more question. I was wondering if you could help me understand what the first part of the Function CreatePlot is doing, specifically the vntCol part. I've included the code below for reference. Thank you.

Please Login or Register  to view this content.

Okay, that makes sense, thank you!

Is there anything in that part that I would need to adapt to work with column headers it's looking for? I ask this because when I run this function, and a match is not found, I get a Run-time error '1004' Unable to get the Match property of the WorksheetFunction class.

Awesome, thanks again Andy! I feel silly asking another questions, especially since you've already been so patient with me, but I really want to understand what's going on so I can figure this out.

Making the change you posted above, I get "Unable to locate data..." if the column headers do not match what is needed. However, after I press OK, it does not exit the function. Instead, it goes and tries to create the first scatterplot with the missing data. Now I get "Run-time error '91': Object variable or With block variable not set". When I debug, the code is highlighted in the second part, where it's creating the first scatterplot, at "With chtTemp.Chart"

Please Login or Register  to view this content.

Awesome, works perfectly!!! Thanks again for all your help Andy!