troubleshooting Question

How can I add to my code to have it check 2 cells to see if they contain data if so proceed with rest of code

Avatar of gdunn59
gdunn59 asked on
Microsoft ExcelVisual Basic ClassicSpreadsheets
2 Comments2 Solutions92 ViewsLast Modified:
I have the following code that loops through a spreadsheet and gathers data from 4 different columns.  I need to have it check to see if Columns 12 OR 13 contain data, if so then collect that data and put in Sheet2, if those 2 cells are empty, then skip over them and go to the next row and check.

Everything is working right now, except it is putting all rows 4 different columns in Sheet2 and I just need it to put them in Sheet2 if there is data in Columns 12 OR 13.

How can I revise my existing code to do this?  Thanks!

Here is my code:
Private Sub cmdSubmitChangesTo3E_Click()
' create an XML file from an Excel table
    Dim varInvIndex As String
    Dim varInvElectHistID As String
    Dim varDateSubmitted As String
    Dim varComments2 As String
    Dim i As Long
    Dim wb1 As Excel.Workbook
    Dim k As Long

    ActiveWorkbook.Worksheets("Sheet1").Activate

    i = 8
    k = 2

    Do Until i > Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ' varInvIndex
        Worksheets("Sheet2").Range("A" & k) = Cells(i, 5).Value
        'varInvElectHistID
        Worksheets("Sheet2").Range("b" & k) = Cells(i, 6).Value
        ' varDateSubmitted
        Worksheets("Sheet2").Range("c" & k) = Cells(i, 12).Value
        'varComments2
        Worksheets("Sheet2").Range("d" & k) = Cells(i, 13).Value

        k = k + 1
        i = i + 1
    Loop

Worksheets("Sheet2").Range("A1").Value = "InvIndex"
Worksheets("Sheet2").Range("B1").Value = "InvElectHistID"
Worksheets("Sheet2").Range("C1").Value = "Date Submitted"
Worksheets("Sheet2").Range("D1").Value = "Comments"

Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:A").Select
Selection.ColumnWidth = 11.57
Cells.Select
Cells.EntireRow.AutoFit
Rows("2:2").Select
'With ActiveWindow
'    .SplitColumn = 0
'    .SplitRow = 1
'End With

Worksheets("Sheet2").Select
ActiveWindow.FreezePanes = False
Worksheets("Sheet2").Range("A2").Select
ActiveWindow.FreezePanes = True

Worksheets("Sheet2").Range("A2").Select

ActiveWorkbook.Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A8").Select

End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros