+ Reply to Thread
Results 1 to 5 of 5

Conditional Statement In VBA

Hybrid View

laguna92651 Conditional Statement In VBA 03-16-2012, 11:31 PM
AlphaFrog Re: Conditional Statement In... 03-17-2012, 02:34 AM
laguna92651 Re: Conditional Statement In... 03-18-2012, 01:00 PM
snb Re: Conditional Statement In... 03-18-2012, 01:37 PM
laguna92651 Re: Conditional Statement In... 03-21-2012, 12:46 PM
  1. #1
    Registered User
    Join Date
    01-18-2012
    Location
    Los Angeles USA
    MS-Off Ver
    Excel Office 365
    Posts
    80

    Re: Conditional Statement In VBA

    Thanks very much for your help, if I had additional lines I assume I would just include the corresponding number of If statements?

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Conditional Statement In VBA

    Sub MoveRecord()
      with sheets("SalesData").Cells(Rows.Count, 1).End(xlUp)
        for j=1 to 4
          if cells(j+20,3).value<>"" then .offset(j).Resize(1, 10) = Array([F6], [F6], [F5], [A18], [b18], [c10], cells(j+20,3), cells(j+20,4), cells(j+20,6), cels(j+20,2))
        next
      end with
    End Sub
    Last edited by snb; 03-18-2012 at 03:40 PM.



  3. #3
    Registered User
    Join Date
    01-18-2012
    Location
    Los Angeles USA
    MS-Off Ver
    Excel Office 365
    Posts
    80

    Re: Conditional Statement In VBA

    I ended up with the code below, which seems to work okay. Thanks for the help. Now to the details.

    1. The database is a table, how do I maintain the table as a table, if I post records to the bottom of the table with the shown code, it has some manually added records, those records no longer take on the properties of the table? Is there a way to force the table to remain a table after I post a record to it?

    2. Secondly, how would I skip cells in the record, there are cells in the table that have vlookups and formulas in them that I would like to apply to the posted record. So I would like to post nothing in a column so I can use the table's formulas, then post the next form cell in the next database column.

    3. How would I tell Excel which column to start entering the data from the form? Right now I'm sending a cell that is blank that goes into the first column. The first column is blank because I'm using Freeze First Column. The actual new record data starts at column 2.

    4. Resize(1,13) Does the the 1 here determine how many copies to make of data?

    Sub MoveRecord() 
    
    Dim Increment As Integer 'looping variable 
       Dim WSF As Worksheet ' Invoice worksheet 
       Dim WSD As Worksheet ' SalesData worksheet 
       Set WSF = Worksheets("Invoice") 
       Set WSD = Worksheets("SalesData") 
       NextRow = WSD.Cells(Rows.Count, 2).End(xlUp).Row + 1 
    
    For Increment = 19 To 34 ' Test rows 19 to 34 of Invoice, if order entry line has something in it. 
       If WSF.Cells(Increment, 3) <> "" Then 
    
          WSD.Cells(NextRow, 1).Resize(1, 14).Value = Array([D4], [F4], [F4], [F3], [A16], [B16], [c9],
                 Cells(Increment, 3), Cells(Increment, 4), Cells(Increment, 11), Cells(Increment, 2), 
    Cells(Increment, 12), Cells(Increment, 13), Cells(Increment, 14))
    
    NextRow = WSD.Cells(Rows.Count, 2).End(xlUp).Row + 1 
    
    End If 
    
    Next Increment
    Last edited by laguna92651; 03-21-2012 at 01:53 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1