Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Follow on to earlier Excel VBA question

Posted on 2014-10-17
2
103 Views
Last Modified: 2014-10-17
In a different question, a follow on to this :
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28538139.html
I now need loop through all rows in a worksheet, output cell values in a specified column order based on values in Row 1:
Study	Filo	Mean	Olp	Std Dev	Bell
Sun	9	29.998	77	33.887	G
Mercury	66	30.686	29	37.03	R
Venus	53	993.09	65	643	H
Earth	44	44.099	22	34.06	J
Mars	78	77.94	90	22.796	B

Open in new window

For instance, beginning with row 2, output the cell value of the column with the 'label' of Mean [29.988]
then the cell value of the column of 'Std Dev' [33.887]
then the cell value of the column of 'Study' [Sun]
Then continue processing the remainder of the rows in the worksheet.
At first, it seems to be simple, alas not so much: lookup the value of row [r], column[x] where column heading is [Mean].  Repeat for heading [Std Dev] and then heading [Study], regardless of the column order.
and I have no way of knowing the sheet name,  or even how many columns there are in the sheet, just that the columns have those headings.  I have tried hlookup, but can't seem to get it to work when searching for the heading columns, and I am not convinced that hlookup is the correct function to use.
Hope this is clear, and thanks for looking.
0
Comment
Question by:Program652
2 Comments
 
LVL 7

Accepted Solution

by:
slubek earned 500 total points
ID: 40386756
Hi, again :^)

If I understand Your problem correctly, declare three variables:
sStudy, sStdev, sMean as String
Find their values first, then create output in proper order:
    While Cells(iRow, 1) > ""
        sXML = sXML & "<row id=" & Q & iRow & Q & ">"

		For icol = 1 To iColCount - 1
			select CASE Cells(iCaptionRow,icol)
			case "Study"
				sStudy = Trim$(Cells(iRow, icol))
			case "Mean"
				sMean = Trim$(Cells(iRow, icol))
			case "Std Dev"
				sStdDev = Trim$(Cells(iRow, icol))
			end select
		Next

		sXML = sXML & "<Study>" & sStudy & "</Study>"
		sXML = sXML & "<Mean>" & sMean & "</Mean>"
		sXML = sXML & "<Std Dev>" & sStdDev & "</Std Dev>"
		
        sXML = sXML & "</row>"
        iRow = iRow + 1
    Wend

Open in new window

0
 

Author Comment

by:Program652
ID: 40386795
Well, made that look easy... <grin>
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question