Solved

Follow on to earlier Excel VBA question

Posted on 2014-10-17
2
99 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
Comment Utility
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
Comment Utility
Well, made that look easy... <grin>
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now