0

As vba learner,I have designed a simple code for VBA.

I have 3 columns- Parent Job, Child Job , and Details.

Under parent jobs, I have a number of child jobs.

For each parent job in the first column, I am trying to insert all its child jobs in the second columns, going down by 1 Row. Both parent jobs and child Jobs have some details in the third column. I have designed an user form so far to achieve my objective. I am inserting my data using two buttons for the parent job and child job respectively.

Below are screenshots of what I am trying to do.(data manually inserted here) enter image description here

And here is my current output:

enter image description here

So far, I am able to move to a new row for the Child job.

But for the Parent Job, I am unable to move to an immediate new row after the child meaning that instead of filling in data for the new parent job just below the last child job,the row below the parent job fills in whenever I click the Register parent button multiple times, as you can see from my current output.

I played with the line stating ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row in many ways but I am still unable to achieve the objective. As a beginner, I still could not achieve my objective output even after several hours.

Below is my full Vba code I have come up with:

Private Sub btn_registerChildJob_Click()
Application.DisplayAlerts = False
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Cells(erow, 1) = ""
Cells(erow, 2) = "   " + TextBox2.Text
Cells(erow, 3) = "Test Data"



ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub



Private Sub btnResgiterParentJob_Click()
Application.DisplayAlerts = False
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Font.Bold = True
Cells(erow, 1).Font.Color = vbRed
Cells(erow, 1) = TextBox1.Text

Cells(erow, 3) = "Test Data"

End Sub

Hi all, please can anyone suggestion where do I change in my code to achieve my objective?

Habeeb E Sadeed
  • 49
  • 7
  • If I understand you correctly, maybe you want to try something like this : `par = TextBox1.Text: chi = TextBox2.Text` next line `Set c = Columns(1).Find(par, lookat:=xlWhole, MatchCase:=True)` next line `Set c = c.End(xlDown)` next line `Range(c, c.Offset(0, 2)).Insert Shift:=xlDown` next line `With c.Offset(-1, 0)` next line `.Offset(0, 1).Value = chi: .Offset(0, 2).Value = "Test Data"` next line `End With` next line `End Sub` – karma Nov 14 '22 at 11:22
  • The sub find the cell which contains of your TextBox1 (par variable) as c variable. Then it use end(xldown) to get the next parent name. Insert one row, then put the TextBox2 (chi variable) and the "TestData". – karma Nov 14 '22 at 11:26

0 Answers0