You are here:

Excel/VBA Next available row

Advertisement


Question
QUESTION: Hello,

Is there any to create code that will place values in the next available row when there are empty cells above in the same column? I am working with a worksheet linked via an API and the sheet is already formatted with headings. SO I would like to start filling in cells at A9 even though there are some cells above which are empty.

Thank you.

ANSWER: I'm not sure I understand the ramifications of the cells above which are empty.

The method I use in 99% of my projects is to look UP column A for the last entry, then mark the next row as my "NR" or "next row".  You can use any column you know always has data in used rows, for me it's column A:


Dim NR as Long

NR = Range("A" & Rows.Count).End(xlUp).Row + 1

'Start filling in the next row:
Range("A" & NR).Value = Date
Range("B" & NR).Value = Time
'etc....

---------- FOLLOW-UP ----------

QUESTION: I stayed up all night trying to code this. I finally scrapped it and wrote it in a way in which I think will be clear to you. Please help, I'm exhausted.

'Sub CFB_Buy()
   'If ThisWorkbook.Worksheets("DashBoard").Range("F7") <> 3 Or ThisWorkbook.Worksheets("DashBoard").Range("J7") <> 1 Then Exit Sub
   'If ThisWorkbook.Worksheets("DashBoard").Range("F7") = 3 Or ThisWorkbook.Worksheets("DashBoard").Range("J7") = 1 Then begin
   'If the Sum of This Workbook.Worsheet("System 1").Range("T110:T115") = 1 Then
   'Execute the OE sub below, the sub results Should wind up in ThisWorkbook.Worksheets("Order Entry") Next available row
   'If there is a match between Thisworkbook.Worksheets("Accounts") column "A" and ThisWorkbook.Worksheets("Dashboard").Range("D7") grab the value of column "F" of that matching Row (Minus .1) and place that value in ThisWorkbook.WorkSheets("Dashboard").Range("K7")
   'If there is a match between Thisworkbook.Worksheets("Accounts") column "A" and ThisWorkbook.Worksheets("Dashboard").Range("D7") grab the value of column "F" of that matching Row (Plus .1) and place that value in ThisWorkbook.WorkSheets("Dashboard").Range("L7")
'End Sub

Sub OE()
Dim NR As Long
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & NR).Value = "Submit"
Range("B" & NR).Value = "1234-5678"
Range("C" & NR).Value = "Buy"
Range("D" & NR).Value = ThisWorkbook.Worksheets("DashBoard").Range("E7").Value 'Quanity
Range("E" & NR).Value = ThisWorkbook.Worksheets("DashBoard").Range("D7").Value 'Name
Range("F" & NR).Value = "Limit"
Range("G" & NR).Value = ThisWorkbook.Worksheets("DashBoard").Range("O7").Value + 0.01 'Price
Range("H" & NR).Value = "Day"
Range("I" & NR).Value = "No"
End Sub

ANSWER: So something like this:


Option Explicit

Sub CFB_Buy()
Dim wsOE As Worksheet, wsDB As Worksheet
Dim NR As Long, vFIND As Range

Set wsDB = ThisWorkbook.Sheets("Dashboard")
Set wsOE = ThisWorkbook.Sheets("Order Entry")

   If wsDB.Range("F7") = 3 Or wsDB.Range("J7") = 1 Then
       If WorksheetFunction.Sum(ThisWorkbook.Sheets("System 1").Range("T110:T115")) = 1 Then
         With wsOE
         NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
         .Range("A" & NR).Value = "Submit"
         .Range("B" & NR).Value = "1234-5678"
         .Range("C" & NR).Value = "Buy"
         .Range("D" & NR).Value = wsDB.Range("E7").Value          'Quantity
         .Range("E" & NR).Value = wsDB.Range("D7").Value          'Name
         .Range("F" & NR).Value = "Limit"
         .Range("G" & NR).Value = wsDB.Range("O7").Value + 0.01   'Price
         .Range("H" & NR).Value = "Day"
         .Range("I" & NR).Value = "No"
         End With
       End If
       
       On Error Resume Next
       Set vFIND = ThisWorkbook.Worksheets("Accounts").Range("A:A").Find(wsDB.Range("D7"), LookIn:=xlValues, LookAt:=xlWhole)
       If Not vFIND Is Nothing Then
         wsDB.Range("K7") = vFIND.Offset(, 5).Value - 0.01
         wsDB.Range("L7") = vFIND.Offset(, 5).Value + 0.01
         Set vFIND = Nothing
       End If
   End If

End Sub


---------- FOLLOW-UP ----------

QUESTION: Thank you for all the help. Last one from me.

See commented area for what I am trying to do.

Option Explicit
Sub Cancel_Order()
Dim wsOE As Worksheet, wsDB As Worksheet
Dim vFIND As Range

Set wsDB = ThisWorkbook.Sheets("Dashboard")
Set wsOE = ThisWorkbook.Sheets("Order Entry")

If wsDB.Range("F7") > 0 And wsDB.Range("J7") = 1 Then
  Set vFIND = ThisWorkbook.Worksheets("Account 1234-5678").Range("E:E").Find(wsDB.Range("D7"), LookIn:=xlValues, LookAt:=xlWhole)
      If Not vFIND Is Nothing Then
' This summarizes what I am trying to do.
' Find the match for wsDB.range("D7") in wsOE
' If after finding the match, look in same row column "M" for "Out of Stock", if true then
' write "Cancel" in Column A of same row
' otherwise do nothing

End Sub

Answer
Option Explicit

Sub Cancel_Order()
Dim wsOE As Worksheet, wsDB As Worksheet
Dim vFIND As Range

Set wsDB = ThisWorkbook.Sheets("Dashboard")
Set wsOE = ThisWorkbook.Sheets("Order Entry")

If wsDB.Range("F7") > 0 And wsDB.Range("J7") = 1 Then
 'Find the match for wsDB.range("D7") in wsOE, if found and column M is "Out of Stock", write "Cancel" in column A
   Set vFIND = wsOE.Range("E:E").Find(wsDB.Range("D7"), LookIn:=xlValues, LookAt:=xlWhole)
   If Not vFIND Is Nothing Then
       If wsOE.Range("M" & vFIND.Row).Value = "Out of Stock" Then wsOE.Range("A" & vFIND).Value = "Cancel"
   End If
End If

End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.