• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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
0
drezner7
Asked:
drezner7
  • 6
  • 6
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
As long as your loop has a terminator to identify the end of range then change the while to an if ... but it is important to identify and exit strategy of course.

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
0
 
drezner7Author Commented:
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

 
0
 
drezner7Author Commented:
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  

0
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.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
No my point was removal of the do loop as it stands and then looping as required so for example

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

Open in new window

0
 
drezner7Author Commented:
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>")
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Your original was only testing on colun a ... How many cells do you want to test on each iteration?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
drezner7Author Commented:
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
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
drezner7Author Commented:
for the column
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Then look to your code lines where:

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>")

can be reproduced as

If UCase(Left(colA,2))= "XB" Then
        oOut.WriteLine("  <XB_data>")
        if colB <> "" then oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        if colC <> "" then oOut.WriteLine("  <LSACONXB>" & colC & "</LSACONXB>")
        if colD <> "" then oOut.WriteLine("  <ALTLCNXB>" & colD & "</ALTLCNXB>")
        if colE <> "" then oOut.WriteLine("  <LCNTYPXB>" & colE & "</LCNTYPXB>")

And simply repeat for every writeline.
0
 
drezner7Author Commented:
Thank you very much.... Nice ...!! You just saved me tons of work
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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