0

New to VBA here. Almost done with this project I am working on and missing one piece I cant figure out with VBA.

I am using a user form to input data into a sheet. I have a condition that if a column range (I named it) is > 0, take value from another column and use that value to add that many rows to another table on another sheet.

I got all that working great. The roadblock I am having is I also need the row number of the activecell from the source sheet (the one I use the user form with) to also be inserted into column A of all the new rows inserted when the code executes.

I need this done because I have formulas in cells B through E that calculate based on the value in row A; and i need the value in row a to be the row number from the row I just inserted into my source sheet (hens the "active cell" mentality).

Clear as Mud? I hope I explained this all correctly and would really appreciate any help I can get on this. I have provided the code. Its the last part I need help with. I think its a loop I have to use but I haven't used loops in vba yet.

 Sub AddRowsToTable()

 Dim targetTable As ListObject
 Dim valueLastRowLastCol As Long

 Set targetTable = Range("TblDateRng").ListObject

' Get last row, last column value
valueLastRowLastCol = Intersect(Rows(ActiveCell.Row), Range("Duration")).Value

'Add as many rows as the number in last column, last row of source table to target table
targetTable.Resize targetTable.HeaderRowRange.Resize(targetTable.ListRows.Count + valueLastRowLastCol 
+ 1)

'Execute the column number of the active cell from the source sheet, and places it in column (A) of 
new rows added from code above
For Each cell In targetTable.Range("A:A")
If targetTable.Range("A:A") = "" Then
Range("A:A").Value = ActiveCell.Row

MsgBox "Success"
End If
Next
End Sub
Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
Dan Kirchner
  • 45
  • 6
  • 1
    I'm making a guess that you might want to change `If targetTable.Range("A:A") = "" Then Range("A:A").Value = ActiveCell.Row` to `If cell = "" then cell.value = ActiveCell.Row` – Christofer Weber Mar 03 '20 at 20:32
  • Except - [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) instead of looping through the entire column. – BigBen Mar 03 '20 at 20:38
  • Dan, [Posting](https://stackoverflow.com/posts/60514993/edit) some sample data / and screenshots would speed things up – Ricardo Diaz Mar 03 '20 at 21:22
  • Hey Ricardo! Welcome back to helping me out lol. Your last code you have me on my other question was a life saver. As you can probably tell from this post I'm still using a good portion of it. I've made the suggested edits to the code but the module seems to want to skip over that last part for some reason. As soon as I can I'm going to do some troubleshooting and see if I can figure it out. Ill keep you updated as soon as I can. If you have any other suggestions I'll take them. I don't have enough reputation to post pictures. – Dan Kirchner Mar 04 '20 at 00:30
  • Ricardo. I was able to get it to work. Thank you again! IDK why it would skip the code like I mentioned in my last post. I start from scratch and it works now. Funny things happen I guess. – Dan Kirchner Mar 05 '20 at 15:59

0 Answers0