Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

Follow on to earlier Excel VBA question

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
Program652
Asked:
Program652
1 Solution
 
slubekCommented:
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
 
Program652Author Commented:
Well, made that look easy... <grin>
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now