How to add Rows automatically based on Cell Value In Excel?
Solved/ClosedTrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 12, 2022 at 11:32 AM
- How to insert rows in excel automatically based on cell value without vba
- How to insert rows in excel automatically based on cell value - Best answers
- Excel formula to add rows based on cell value - Best answers
- Number to words in excel formula without vba - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to insert photo in word for resume - Guide
- How to insert check mark in word - Guide
- Insert gif in excel - Guide
2 responses
Aug 30, 2022 at 12:06 PM
Hi Raj,
The code below does the following:
When you confirm the value in Sheet 2 column E, the value from column A will be looked up in Sheet 1 column A. The row (Column A:E) from Sheet 2 will be copied and inserted below the found value of Sheet 1, without the values from columns A and B.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("E")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Dim mFind As Range
Set mFind = Sheets("Sheet 1").Columns("A").Find(Target.Offset(0, -4).Value)
If Not mFind Is Nothing Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "E")).Copy
If Sheets("Sheet 1").Range("A" & mFind.Row + 1).Value = vbNullString _
And Sheets("Sheet 1").Range("A" & mFind.Row).End(xlDown).Row < 1000000 Then
With Sheets("Sheet 1").Range("A" & mFind.Row).End(xlDown)
.Insert
.Offset(-1, 0).ClearContents
.Offset(-1, 1).ClearContents
End With
Else
With Sheets("Sheet 1").Range("A" & mFind.Row + 1)
.Insert
.Offset(-1, 0).ClearContents
.Offset(-1, 1).ClearContents
End With
End If
End If
End Sub
Best regards,
Trowa
Updated on Aug 31, 2022 at 02:01 AM
Hi Trowa,
Thank you so much for your prompt response.
It's working fine as I expected. But in this logic, I should enter the value by manual in Sheet 2 column E. Is there any possibility to add the values automatically in excel?
When I open the excel the value will be transferred to sheet 1 from Sheet 2. I have inserted the add-on in excel. So, the value will update sheet 1 and sheet 2 when I open the excel file.
Here the user is not able to make any of the changes in Sheet 1 & Sheet 2.
Regards,
Raj
Sep 1, 2022 at 11:33 AM
Hi Raj,
When you open the file, and the rows from Sheet 2 will be copied to Sheet 1, will Sheet 2 be cleared? In other words, do we need to be wary of duplicates?
The code below copies the rows from Sheet 2 to Sheet 1, when you open the file. Sheet 2 will be left untouched for you to see if result is correct. Note that this method will create duplicates when Sheet 2 isn't cleared upon re-opening your file.
Here is the code, which needs to be placed under ThisWorkbook):
Private Sub Workbook_Open()
Dim mFind As Range
Sheets("Sheet 2").Select
For Each cell In Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
Set mFind = Sheets("Sheet 1").Columns("A").Find(cell.Value)
If Not mFind Is Nothing Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "E")).Copy
If Sheets("Sheet 1").Range("A" & mFind.Row + 1).Value = vbNullString _
And Sheets("Sheet 1").Range("A" & mFind.Row).End(xlDown).Row < 1000000 Then
With Sheets("Sheet 1").Range("A" & mFind.Row).End(xlDown)
.Insert
.Offset(-1, 0).ClearContents
.Offset(-1, 1).ClearContents
End With
Else
With Sheets("Sheet 1").Range("A" & mFind.Row + 1)
.Insert
.Offset(-1, 0).ClearContents
.Offset(-1, 1).ClearContents
End With
End If
End If
Next cell
End Sub
Let us know if further alterations are desired.
Best regards,
Trowa
Sep 2, 2022 at 08:22 AM
Hi TrowaD,
Thank you very much for your quick response to my request.
Sheet 1 is the static value. It will not change. But Sheet 2 is dynamic. So, The value and rows are will be increased based on the transaction each day.
I would like to bring the sum of the company and transaction value based on currency. Is it possible to bring the summation value and the "Balance Company Currency" always USD.
Please ignore the previous snapshot.
I have attached the updated screenshot below for your reference and the excel file as well (In the excel file dummy data only I entered).
I don't think this is possible to get the desired output. I have tried a lot of options with excel format. But I can't.
https://ln5.sync.com/dl/a5dd85b90/u9tgiqkn-kwgasdv5-kgzka4ze-2627it5m
Regards,
Raj
Sep 5, 2022 at 12:15 PM
Hi Raj,
As long as I understand what you are trying to achieve and there is logic for Excel to process your data, I'm sure we can work something out.
I'm just having trouble with finding the logic.
Looking at the yellow part, column D result you have: 100 USD, -50 ILS. Where I would expect it to be: 100 USD, 50 ILS.
Orange part makes sense.
Grey part column D, you have 16,52 EUR, -100.096.000 ILS. Where I would expect: 16,52 EUR, (-100.096.000-16,52) ILS.
Grey part column G, you have 2500 USD. Where I would expect: 500 USD.
Green part column D, here you mention a zero value not mentioned before, do you want zero values to show or not?
Green part column E, ILS changed to USD. Is that what you meant by:'the "Balance Company Currency" always USD'?
Also cell A9 of sheet2 is X11112, while the rest of the orange values are X11111, typo?
Please shine some logical light on these points.
Best regards,
Trowa
Updated on Sep 6, 2022 at 11:28 PM
Hi Trowa,
Thank you very much for this.
Please see my comment in every line within the bracket.
1.) Looking at the yellow part, column D result you have: 100 USD, -50 ILS. Where I would expect it to be: 100 USD, 50 ILS.
The orange part makes sense. - (In the Output sheet the value 100.00 USD and -50.00 ILS comes from sheet 2 based on currency code (e.g: Column G (ILS): 100.00 and - 50.00 = 50.00 ILS )So, the 50.00 ILS I displayed in Output sheet Column D near ILS currency.
2. Does) Orange part makes sense? - ( Yes, The same logic applied to all the Columns).
3.) Grey part column D, you have 16,52 EUR, -100.096.000 ILS. Where I would expect: 16,52 EUR,(-100.096.000-16,52) ILS? - (Based on currency code the values are summed up from sheet 2 to Output sheet to the respective columns).
4.) In Green part column D, here you mention a zero value not mentioned before, do you want zero values to show or not? - (This is not an issue, If we can able to see the 0.00 value in an empty column is fine otherwise this is not required (Displaying the 0 ILS in the column is a typo).
5.) Green part column E, ILS changed to USD. Is that what you meant by:'the "Balance Company Currency" always USD'? -(Sorry! in column E, ILS changed to USD is a typo, and the "Balance Company Currency" currency code is always USD.
Also, cell A9 of sheet2 is X11112, while the rest of the orange values are X11111, typo? - (Yes, it's a typo).
Thank you in advance!
Regards,
Raj
Sep 12, 2022 at 11:32 AM
Hi Raj,
How about using formula's? For sheet1 D9 use:
=SUMIF(Sheet2!$F$5:$F$8,"USD",Sheet2!G$5:G$8)
When inserting a row on sheet2, the formula will automatically adjust.
Drag the formula one cell down and replace USD with ILS. Copy both cells and select, while holding down CTRL, F9:F10, H9:H10 etc. and paste the formula.
For sheet1 E9 use:
=SUM(Sheet2!J$5:J$7)
Best regards,
Trowa
