Solved

Cell tha that are empty

Posted on 2011-09-09
12
400 Views
Last Modified: 2012-05-12
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
Comment
Question by:drezner7
  • 6
  • 6
12 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36515412
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
 

Author Comment

by:drezner7
ID: 36520375
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
 

Author Comment

by:drezner7
ID: 36520449
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36520920
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
 

Author Comment

by:drezner7
ID: 36522257
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36522276
Your original was only testing on colun a ... How many cells do you want to test on each iteration?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36522287
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
 

Author Comment

by:drezner7
ID: 36522544
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36523365
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
 

Author Comment

by:drezner7
ID: 36524183
for the column
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 36524262
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
 

Author Closing Comment

by:drezner7
ID: 36524362
Thank you very much.... Nice ...!! You just saved me tons of work
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

16 Experts available now in Live!

Get 1:1 Help Now