Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 25th, 2008, 03:54 PM
Registered User
 
Join Date: Oct 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default compare a cell with nothing

i am sorry about my english.

I am trying to write a vba code to insert some text in an array of cells (as is show below).

There are 2 steps:
1 - i want find if in a certain cell is empty, and if the cell is empty to write something in that cell and stop the private sub.
2- if that cell have a text i want to go to the next cell and do again 1st step

i try this but doesn't work.


Private Sub CommandButton16_Click()
Dim i As Integer
For i = 28 To 33 Step 1
If Cells(i, 1) = "" Then Cells(28, 1) = "SO2": GoTo 10
If Cells(i, 2) = "" Then Cells(28, 1) = "SO2": GoTo 10
If Cells(i, 4) = "" Then Cells(28, 1) = "SO2": GoTo 10
If Cells(i, 7) = "" Then Cells(28, 1) = "SO2": GoTo 10
If Cells(i, 8) = "" Then Cells(28, 1) = "SO2": GoTo 10
If Cells(i, 9) = "" Then Cells(28, 1) = "SO2": GoTo 10
If Cells(i, 10) = "" Then Cells(28, 1) = "SO2": GoTo 10
Next
10 End Sub


i hope is clear what i want.
thanq!

 
Old October 25th, 2008, 05:17 PM
Registered User
 
Join Date: Aug 2008
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I hope I understood your problem clearly. I have coded the example for variable # rows and variable # columns.

Option Explicit
Option Compare Text
Private Sub FindEmpty()
    Dim i As Integer
    Dim j As Integer

    Dim intMinRow As Integer
    Dim intMaxRow As Integer
    Dim intMinCol As Integer
    Dim intMaxCol As Integer

    Dim objSheet As Worksheet
'***
'* . set row and column limits
'***
    intMinRow = 28
    intMaxRow = 33
    intMinCol = 1
    intMaxCol = 3
'***
'* . loop through all rows of each column
'* . if (row) cell is empty, add text and process next column
'***
    Set objSheet = ThisWorkbook.Worksheets(1)
    With objSheet
        For j = intMinCol To intMaxCol
            For i = intMinRow To intMaxRow
                If Len(Trim$(.Cells(i, j).Value)) = 0 Then
                    .Cells(i, j).Value = "Inerted Text"
                    Exit For
                End If
            Next i
        Next j
    End With

    Set objSheet = Nothing

End Sub
 
Old October 26th, 2008, 12:35 PM
Registered User
 
Join Date: Oct 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks a lot.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare numbers and letters in same cell EricB123 Excel VBA 1 January 21st, 2007 03:30 PM
I need to refer a cell within a cell like =RC[ RC2 chakravarthi_os Excel VBA 1 September 24th, 2006 08:19 AM
compare these date fields and compare and get the susanring Oracle 1 July 24th, 2006 04:58 PM
How to compare cell contents with a character? danwes Excel VBA 5 June 8th, 2005 11:36 AM
Lose cell Text when editing cell in VSFlexGrid 6 bobcratchet VB How-To 0 July 30th, 2004 09:32 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.