zeetec
asked on
Excel VBA script
I need some help writing a visual basic script to run in Excel on output from a database program. The logic would be as follows.
find "D" in column A and set x to the row number
if C(x+1) is not null then copy Ax and Bx to A(x+1) and B(x+1) respectively
repeat
Essentially it just needs to copy the cells from column A and B down one row if there is a value in column C on that next row.
I can usually cobble together vbscript ok but as I have never worked with excel VBA I am having difficulty and I can't seem to find examples to work from with this so any help would be appreciated.
find "D" in column A and set x to the row number
if C(x+1) is not null then copy Ax and Bx to A(x+1) and B(x+1) respectively
repeat
Essentially it just needs to copy the cells from column A and B down one row if there is a value in column C on that next row.
I can usually cobble together vbscript ok but as I have never worked with excel VBA I am having difficulty and I can't seem to find examples to work from with this so any help would be appreciated.
ASKER
Hi Rspahitz,
Thanks for your suggestion but I need to run a recursive script to update the data in one step. Trying to use formulas is going to be very fiddly if not impossible with the main problem being that Ax and Bx may need to be copied down anywhere from 1 to 100 or more times depending on whether there is data in column c in the row below. Unless I am missing something, your formulas are useful only if you do filter copy filter copy filter copy etc which is not an option. I hope that make sense?
Thanks for your suggestion but I need to run a recursive script to update the data in one step. Trying to use formulas is going to be very fiddly if not impossible with the main problem being that Ax and Bx may need to be copied down anywhere from 1 to 100 or more times depending on whether there is data in column c in the row below. Unless I am missing something, your formulas are useful only if you do filter copy filter copy filter copy etc which is not an option. I hope that make sense?
You should be able to apply the formula as far down as needed, filter once, then copy all and paste apply the results in one shot. However, if you plan to do this more than once, then that might be too many steps.
Let me see if I can come up with a VBA solution.
Let me see if I can come up with a VBA solution.
ASKER
Maybe I should give an example because I am not sure if I was clear about the problem. Here is an example of the data before and after the process.
D1234 John Smith $123
$323
$243
D1234 John Smith $123
D1234 John Smith $323
D1234 John Smith $243
Does that help make it clearer?
D1234 John Smith $123
$323
$243
D1234 John Smith $123
D1234 John Smith $323
D1234 John Smith $243
Does that help make it clearer?
This seems to work. notice that this will destroy whatever is located in columns A and B, whenever it find a "D" in column A's previous row and a null in column C.
For that reason, I am putting the values into columns 4 and 5 so you can test it. If you find it does the job as expected, change the 4 and 5 to 1 and 2.
For that reason, I am putting the values into columns 4 and 5 so you can test it. If you find it does the job as expected, change the 4 and 5 to 1 and 2.
Sub UpdateSomeItems()
Dim iRow As Integer
Dim strColumnACell As String
Dim iTransferCount As Integer
iRow = 1
iTransferCount = 0
Do
strColumnACell = Cells(iRow, 1).Value
If Cells(iRow, 1).Value = "" Then
' exit when column A has no value in it
Exit Do
End If
If InStr(strColumnACell, "D") > 0 Then
' Cell in column A contains a capital D
If Cells(iRow + 1, 3).Value <> "" Then
' Transfer cells in column A and B down
iRow = iRow + 1
' copy from column A (1)
Cells(iRow, 4).Value = Cells(iRow - 1, 1).Value
' copy from column B (2)
Cells(iRow, 5).Value = Cells(iRow - 1, 2).Value
iTransferCount = iTransferCount + 1
End If
End If
iRow = iRow + 1
Loop
MsgBox "Done transferring " & iTransferCount & " item" & IIf(iTransferCount = 1, "", "s") & ".", vbOKOnly Or vbInformation
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is perfect. Thank you. The file I have at the moment is 14000 rows and it only took a few seconds to complete so no problem there. Your code will help me greatly in future when working with Excel in VB thanks.
Glad it helped! :)
In Columns D and E put this formula, starting with column 2:
D2:
=if(Search("D",A1)>0, if(C2<>"", A2,""), "")
E2:
=if(D2<>"", B2, "")
Now you should get values in columns D and E only in the places where you want to insert into columns A and B.
Turn on filters and filter D and E so they only show non-blanks.
Copy everything from D and E and paste into A and B.
(I haven't tested it yet, so make a backup first to ensure you can restore A and B as needed.)