| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add in specific cells
Dear volunteers,
I got following script from the discussion group months ago to separate groups of data with two empty rows base on cells in column A. Now I would like to add text and formula in column D and E (in every first empty row) at bottom of each groups, any ideahow can I do that? Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Next Regards |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add in specific cells
Hi
Maybe something like this. I guess the approach for the formulas has to be changed a bit once we know which formulas you need: Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Range("D" & lngRow) = "Total" Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)" Next Regards, Per "Seeker" skrev i meddelelsen ... Dear volunteers, I got following script from the discussion group months ago to separate groups of data with two empty rows base on cells in column A. Now I would like to add text and formula in column D and E (in every first empty row) at bottom of each groups, any ideahow can I do that? Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Next Regards |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add in specific cells
Hi Jessen,
Thanks for your prompt reply. If my group contain one row only, then it is fine, but if more than one row, data in cells of column D & E of the second and onward rows will also changed to Total and the formula, how can I fix it please? "Per Jessen" wrote: Hi Maybe something like this. I guess the approach for the formulas has to be changed a bit once we know which formulas you need: Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Range("D" & lngRow) = "Total" Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)" Next Regards, Per "Seeker" skrev i meddelelsen ... Dear volunteers, I got following script from the discussion group months ago to separate groups of data with two empty rows base on cells in column A. Now I would like to add text and formula in column D and E (in every first empty row) at bottom of each groups, any ideahow can I do that? Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Next Regards |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add in specific cells
Try the below
Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 For lngRow = lngLastRow To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then _ Rows(lngRow).EntireRow.Resize(2).Insert If Range("A" & lngRow) = "" Then Range("D" & lngRow) = "text" Range("E" & lngRow).Formula = "=today()" End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Dear volunteers, I got following script from the discussion group months ago to separate groups of data with two empty rows base on cells in column A. Now I would like to add text and formula in column D and E (in every first empty row) at bottom of each groups, any ideahow can I do that? Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Next Regards |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add in specific cells
Hi Jacob,
I just need to add one more End If, then it works perfectly, Thanks again for both of you guys. Best Regards "Jacob Skaria" wrote: Try the below Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 For lngRow = lngLastRow To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then _ Rows(lngRow).EntireRow.Resize(2).Insert If Range("A" & lngRow) = "" Then Range("D" & lngRow) = "text" Range("E" & lngRow).Formula = "=today()" End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Dear volunteers, I got following script from the discussion group months ago to separate groups of data with two empty rows base on cells in column A. Now I would like to add text and formula in column D and E (in every first empty row) at bottom of each groups, any ideahow can I do that? Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Next Regards |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add in specific cells
Hi Jacob,
What if I would like to add formula of =sum() in column N and O, how should do I decide the dynamic range of each group on the start and end rows please? Regards "Jacob Skaria" wrote: Try the below Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 For lngRow = lngLastRow To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then _ Rows(lngRow).EntireRow.Resize(2).Insert If Range("A" & lngRow) = "" Then Range("D" & lngRow) = "text" Range("E" & lngRow).Formula = "=today()" End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Dear volunteers, I got following script from the discussion group months ago to separate groups of data with two empty rows base on cells in column A. Now I would like to add text and formula in column D and E (in every first empty row) at bottom of each groups, any ideahow can I do that? Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Next Regards |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add in specific cells
Hi
This should do it. You can just change the column reference to put formulas in other columns. Sub aaa() Dim lngRow As Long Range("A1").End(xlDown).Offset(1, 3) = "Total" For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert Range("D" & lngRow) = "Total" End If Next lngRow = Range("D" & Rows.Count).End(xlUp).Row FirstRow = 2 For r = 2 To lngRow If Range("D" & r) = "Total" Then Range("E" & r).Formula = "=sum(E" & FirstRow & ":E" & r - 1 & ")" FirstRow = r + 2 End If Next End Sub Regards, Per "Seeker" skrev i meddelelsen ... Hi Jessen, Thanks for your prompt reply. If my group contain one row only, then it is fine, but if more than one row, data in cells of column D & E of the second and onward rows will also changed to Total and the formula, how can I fix it please? "Per Jessen" wrote: Hi Maybe something like this. I guess the approach for the formulas has to be changed a bit once we know which formulas you need: Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Range("D" & lngRow) = "Total" Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)" Next Regards, Per "Seeker" skrev i meddelelsen ... Dear volunteers, I got following script from the discussion group months ago to separate groups of data with two empty rows base on cells in column A. Now I would like to add text and formula in column D and E (in every first empty row) at bottom of each groups, any ideahow can I do that? Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Next Regards |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add in specific cells
Hi Per Jessen,
Thank you very much, your codes works. Regards "Per Jessen" wrote: Hi This should do it. You can just change the column reference to put formulas in other columns. Sub aaa() Dim lngRow As Long Range("A1").End(xlDown).Offset(1, 3) = "Total" For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert Range("D" & lngRow) = "Total" End If Next lngRow = Range("D" & Rows.Count).End(xlUp).Row FirstRow = 2 For r = 2 To lngRow If Range("D" & r) = "Total" Then Range("E" & r).Formula = "=sum(E" & FirstRow & ":E" & r - 1 & ")" FirstRow = r + 2 End If Next End Sub Regards, Per "Seeker" skrev i meddelelsen ... Hi Jessen, Thanks for your prompt reply. If my group contain one row only, then it is fine, but if more than one row, data in cells of column D & E of the second and onward rows will also changed to Total and the formula, how can I fix it please? "Per Jessen" wrote: Hi Maybe something like this. I guess the approach for the formulas has to be changed a bit once we know which formulas you need: Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Range("D" & lngRow) = "Total" Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)" Next Regards, Per "Seeker" skrev i meddelelsen ... Dear volunteers, I got following script from the discussion group months ago to separate groups of data with two empty rows base on cells in column A. Now I would like to add text and formula in column D and E (in every first empty row) at bottom of each groups, any ideahow can I do that? Dim lngRow As Long For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then Rows(lngRow).EntireRow.Resize(2).Insert End If Next Regards |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add in specific cells
Thanks for your reply, I am glad to help.
/Per "Seeker" skrev i meddelelsen ... Hi Per Jessen, Thank you very much, your codes works. Regards |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Import data to specific cells for a specific row | Excel Discussion (Misc queries) | |||
| Getting specific data into specific cells? | Excel Discussion (Misc queries) | |||
| Form a circle (ie. color specific cells) given specific radius | Excel Programming | |||
| macro to select cells containing specific text and delete all cells but these | Excel Programming | |||
| Delete specific cells contents in a row with some locked cells in the same row | Excel Programming | |||
Linear Mode
