0

I am very novice at Excel-vba, and learn by taking what others have produced and shared online and customizing it to my needs. I have some code that enters data into a spreadsheet at a specific place, and needs to move to the next row for new data. Here is the code I have:

'Code for saving the data in COFF-171 
    Dim iRow2 As Long 
    Dim ws2 As Worksheet 
    Set ws2 = Worksheets("COFF-171")  

'find first empty row in database 
    iRow2 = ws2.Cells.Find(What:="CFDA#", SearchOrder:=xlRows,_
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1  

    With ws2
       .Cells(iRow2, 3).Value = Me.CDFANoTxtBx.Value
       .Cells(iRow2, 4).Value = Me.ProgramNameComboBx.Value 
       .Cells(iRow2, 5).Value = Me.SubrecTxtBx.Value      
       .Cells(iRow2, 6).Value = Me.SubrecExplTxtBx.Value      
       .Cells(iRow2, 7).Value = Me.TGATxtBx.Value      
       .Cells(iRow2, 8).Value = Me.AddtlNotesTxtBx.Value
    End With

This gets me to the first cell for input and enters the data for that row. What it does not do that I need it to do is go to the next row in preparation for a new data entry. The language works in another table I have because the Find is a wildcard. But I need this one to go to a specific spot in the spreadsheet to start. I'd appreciate any help provided.

Community
  • 1
  • 1
Phil
  • 1
  • 2
  • 1
    `iRow2 = iRow2 + 1` should increase the row value. You will probably want to make a `for` loop to work with many rows at once, sequentially. – Daniel Möller May 07 '18 at 18:21
  • Thank you for your solution. I really appreciate it. I have been trying your suggestion in multiple variations, and still cannot get it to move to the next iRow. I've tried with and without the For loop, and it keeps going back to the first empty row and overwriting the previous entry. But I am a novice, and may be putting it in the wrong places. – Phil May 08 '18 at 18:39
  • The line that defines `iRow2` for the first time is searching for empty lines. This line must not be repeated, it should be run only once for the first `iRow2` , then you update `iRow2` yourself. – Daniel Möller May 08 '18 at 18:42
  • That's what I thought, but I don't know how to stop that from occurring. – Phil May 08 '18 at 19:48
  • Don't write it inside the loop.... – Daniel Möller May 08 '18 at 19:49
  • Is "CFDA#" written somewhere in the sheet? – Daniel Möller May 08 '18 at 19:51
  • CFDA# is text inside a cell on the worksheet COFF-171. I have no written inside the loop. I have an "Add" button that adds a line of data to the worksheets when I press it. All of this code is inside that "Add" button. So I think every time I press the "Add" button, all of the code executes, including the line to find the CFDA# text. – Phil May 08 '18 at 20:04
  • Ah... that line is searching for the "CFDA" text, you need a line that searches for a truly empty cell... see here: https://stackoverflow.com/questions/12497804/finding-first-blank-row-then-writing-to-it – Daniel Möller May 08 '18 at 20:06
  • I want to thank everyone for their input. Although it did not solve my problem, I was able to make it work by defining the range within my worksheet that I wanted to populate, and then searching for the first empty row. Here is the code I used: – Phil May 22 '18 at 23:50
  • Dim ws3 As Range Set ws3 = Worksheets("COFF-171").Range("C1:H28") to select the range within the worksheet, and 'find first empty row in database range: What = Find What, SearchOrder = by rows or by columns, 'SearchDirection = Previous for backwards, next for forward, LookIn = Formulas or Values or Comments iRow2 = ws3.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 to search for the first empty row within that range. – Phil May 22 '18 at 23:52

0 Answers0