benajamingeldart
New Member
- Joined
- Dec 10, 2006
- Messages
- 15
Hi folks,
Usually, this forum sorts me out without a special request but i am struggling with the database from hell based on chemical elements.
I would like to copy the chemical elements listed across a single row in a worksheet called "Conversion" based on if the row beneath has a criteria "ppm". This data will be somewhere across rows 1 and 4 but i don't want to put in cell or row ranges to keep it flexible in its search. The list generated could be of any size as well.
Then i want to transpose and paste it into a workbook called ("Test") in a column called "Element" and repeatedly pasted throughout the column immediately below the previous pasting in a loop.
Alternately i have managed to pre-format the "Test" workbook by inserting blank rows based on countif of "ppm" from the "conversion" workbook cell A1 to make a repeated gap for the elements down a list ID's. Below is the code. If it is possible to combine the countif (ppm in sheet"conversion")-insert blank rows (sheet "Test")-transpose/paste data (sheet "Test"- heading "Element") and repeat that would be ideal but i don't mind pulling together a host of sub routines if required.
Sub InsertBlankRows()
Application.ScreenUpdating = False
Dim numRows As Long
Dim r As Long
Dim Rng As Range
Dim lastrw As Long
numRows = Range("A1").Value
'Note: cells(r,1) is same as cells(r,"A")
lastrw = Cells(Rows.count, "A").End(xlUp).Row
Set Rng = Range(ActiveCell, Cells(lastrw, "A"))
For r = Rng.Rows.count To 1 Step -1
' (r+1) to insert AFTER, (r) to insert BEFOE
Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert
Next r
Application.ScreenUpdating = True
End Sub
Thanks.
Usually, this forum sorts me out without a special request but i am struggling with the database from hell based on chemical elements.
I would like to copy the chemical elements listed across a single row in a worksheet called "Conversion" based on if the row beneath has a criteria "ppm". This data will be somewhere across rows 1 and 4 but i don't want to put in cell or row ranges to keep it flexible in its search. The list generated could be of any size as well.
Then i want to transpose and paste it into a workbook called ("Test") in a column called "Element" and repeatedly pasted throughout the column immediately below the previous pasting in a loop.
Alternately i have managed to pre-format the "Test" workbook by inserting blank rows based on countif of "ppm" from the "conversion" workbook cell A1 to make a repeated gap for the elements down a list ID's. Below is the code. If it is possible to combine the countif (ppm in sheet"conversion")-insert blank rows (sheet "Test")-transpose/paste data (sheet "Test"- heading "Element") and repeat that would be ideal but i don't mind pulling together a host of sub routines if required.
Sub InsertBlankRows()
Application.ScreenUpdating = False
Dim numRows As Long
Dim r As Long
Dim Rng As Range
Dim lastrw As Long
numRows = Range("A1").Value
'Note: cells(r,1) is same as cells(r,"A")
lastrw = Cells(Rows.count, "A").End(xlUp).Row
Set Rng = Range(ActiveCell, Cells(lastrw, "A"))
For r = Rng.Rows.count To 1 Step -1
' (r+1) to insert AFTER, (r) to insert BEFOE
Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert
Next r
Application.ScreenUpdating = True
End Sub
Thanks.