drezner7
asked on
Cell tha that are empty
What I would like to do is if a cell in excel is empty I would like to code to skip and move to the next one.. here is my code so far:
iRow = 2
Do While oSheet.Cells(iRow, 1).Value <> ""
colA = oSheet.Cells(iRow, 1).Value
colB = oSheet.Cells(iRow, 2).Value
colC = oSheet.Cells(iRow, 3).Value
But when I add this to it Do While Not IsEmpty (oSheet.Cells(iRow, 1).Value) <> "" ... It stops working. I want it to read the cell and if it is empty just skip it and move on to the next one.
I am reading the rows like this: and the output is in XML format
If UCase(Left(colA,2))= "XA" Then
oOut.WriteLine(" <XA_data>")
oOut.WriteLine(" <EIACODXA>" & colB & "</EIACODXA>")
Thank you
iRow = 2
Do While oSheet.Cells(iRow, 1).Value <> ""
colA = oSheet.Cells(iRow, 1).Value
colB = oSheet.Cells(iRow, 2).Value
colC = oSheet.Cells(iRow, 3).Value
But when I add this to it Do While Not IsEmpty (oSheet.Cells(iRow, 1).Value) <> "" ... It stops working. I want it to read the cell and if it is empty just skip it and move on to the next one.
I am reading the rows like this: and the output is in XML format
If UCase(Left(colA,2))= "XA" Then
oOut.WriteLine(" <XA_data>")
oOut.WriteLine(" <EIACODXA>" & colB & "</EIACODXA>")
Thank you
ASKER
Would it be this with adding the Then at the end of the If ?
iRow = 2
If oSheet.Cells(iRow, 1).Value <> "" Then
But when I add this I get an error of "Loop without do" .. I have never seen this error before please help
iRow = 2
If oSheet.Cells(iRow, 1).Value <> "" Then
But when I add this I get an error of "Loop without do" .. I have never seen this error before please help
ASKER
Here is the code... I am still getting the error 'Loop without do'
iRow = 2
If oSheet.Cells(iRow, 1).Value <> "" Then
colA = oSheet.Cells(iRow, 1).Value
colB = oSheet.Cells(iRow, 2).Value
colC = oSheet.Cells(iRow, 3).Value
If UCase(Left(colA,2))= "XA" Then
oOut.WriteLine(" <XA_data>")
oOut.WriteLine(" <EIACODXA>" & colB & "</EIACODXA>")
iRow = iRow+1
Loop
oExcel.quit
WScript.Echo "Conversion Completed Successfully"
oOut.Close
iRow = 2
If oSheet.Cells(iRow, 1).Value <> "" Then
colA = oSheet.Cells(iRow, 1).Value
colB = oSheet.Cells(iRow, 2).Value
colC = oSheet.Cells(iRow, 3).Value
If UCase(Left(colA,2))= "XA" Then
oOut.WriteLine(" <XA_data>")
oOut.WriteLine(" <EIACODXA>" & colB & "</EIACODXA>")
iRow = iRow+1
Loop
oExcel.quit
WScript.Echo "Conversion Completed Successfully"
oOut.Close
No my point was removal of the do loop as it stands and then looping as required so for example
Chris
Chris
firstrow = 2
lastrow = osheet.Cells(osheet.Rows.Count, 1).End(xlUp).Row
For iRow = firstrow To lastrow
If osheet.Cells(iRow, 1).Value <> "" Then
colA = osheet.Cells(iRow, 1).Value
colB = osheet.Cells(iRow, 2).Value
colC = osheet.Cells(iRow, 3).Value
End If
Next
ASKER
My apologies, but I am still confused,... I am still learning how to code in vbscript. I do not understand loop as required. In my mind I have to loop every Row and column, but skip the 'The oOut.Writeline if the cell is empty.
In this section of the code colD was empty, but it still prints it out the tags in the XML.
If UCase(Left(colA,2))= "XB" Then
oOut.WriteLine(" <XB_data>")
oOut.WriteLine(" <EIACODXA>" & colB & "</EIACODXA>")
oOut.WriteLine(" <LSACONXB>" & colC & "</LSACONXB>")
oOut.WriteLine(" <ALTLCNXB>" & colD & "</ALTLCNXB>")
oOut.WriteLine(" <LCNTYPXB>" & colE & "</LCNTYPXB>")
In this section of the code colD was empty, but it still prints it out the tags in the XML.
If UCase(Left(colA,2))= "XB" Then
oOut.WriteLine(" <XB_data>")
oOut.WriteLine(" <EIACODXA>" & colB & "</EIACODXA>")
oOut.WriteLine(" <LSACONXB>" & colC & "</LSACONXB>")
oOut.WriteLine(" <ALTLCNXB>" & colD & "</ALTLCNXB>")
oOut.WriteLine(" <LCNTYPXB>" & colE & "</LCNTYPXB>")
Your original was only testing on colun a ... How many cells do you want to test on each iteration?
Ie I assume
If UCase(Left(colA,2))= "XB" Then
Is meant to test column d and therefore would assume colA is defined as 1 and that 4 is needed for the column I'd
If UCase(Left(4,2))= "XB" Then
Chris
If UCase(Left(colA,2))= "XB" Then
Is meant to test column d and therefore would assume colA is defined as 1 and that 4 is needed for the column I'd
If UCase(Left(4,2))= "XB" Then
Chris
ASKER
Column A has multiple values for example:
XB
XB
CA
CA
HH
HH
HB
HB
XA
etc...
Once the code reads col A based on what ever it equals it would grab the values to rest of the columns on the right. I think it would be easier If I just show u the whole code... and sample data.
code.txt
sample.xlsx
XB
XB
CA
CA
HH
HH
HB
HB
XA
etc...
Once the code reads col A based on what ever it equals it would grab the values to rest of the columns on the right. I think it would be easier If I just show u the whole code... and sample data.
code.txt
sample.xlsx
So any column can be blank and if blank you want to skip the write line for that specific column ... or for the whole row?
Chris
Chris
ASKER
for the column
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much.... Nice ...!! You just saved me tons of work
iRow = 2
If oSheet.Cells(iRow, 1).Value <> ""
colA = oSheet.Cells(iRow, 1).Value
colB = oSheet.Cells(iRow, 2).Value
colC = oSheet.Cells(iRow, 3).Value
Chris