PART A: VBA Coding In this part, you are given a large data set that should be s
ID: 3673028 • Letter: P
Question
PART A: VBA Coding In this part, you are given a large data set that should be stored in a specific manner since it is used by other units in your company. Namely, the data set should satisfy the following The first observation is in row 5. The last one is in row 1521 . In each row, there are four entries: The first one is supposed to be in Column B and a three-letter abbreviation. The second one is supposed to be in Column C and a four digit number. The last two are supposed to be in Columns D and E and are fractional numbers between 0 and 1. 4 6351 0.422385 0.116315 5366 ANK 0.325841 0.20465 3255 0.614506 0.24869 3407 IZM 0.545942 0.882066 ANK OSM MER 5243 0.021989 0.185686 4361 0.291526 0.791219 355 0.097051 0.864011 5390 0.007621 0.54295 10 12 13 14 5235 BUR 0.08629 0.398418 BUR 487 0.800508 0.063262 Above you are given the first ten rows in this data set (The whole data set is stored in HW5VBA.xlsm, which is available on Canvas). As it can be seen above, there are rows which do not satisfy the requiremento due to an error in the data entry proceso. Particularly, in thece incorrect rows, the third and the fourth entries are shifted to the right by two column, and the first two cells are swapped.Explanation / Answer
Sub MoveCell(rownum As Integer)
Range("F" & rownum, "G" & rownum).Select
Selection.Cut
Range("D" & rownum).Select
ActiveSheet.Paste
End Sub
Sub swapBnC(rownum As Integer)
valueB = Cells(rownum, "B").Value
ValueC = Cells(rownum, "C").Value
Cells(rownum, "B").Value = ValueC
Cells(rownum, "C").Value = valueB
End Sub
Sub MainLoop()
Dim rownum As Integer
rownum = 5
Do While rownum <= 1521
If Cells(rownum, "D").Value = "" Then
MsgBox (Cells(rownum, "C").Value)
Call MoveCell(rownum)
Call swapBnC(rownum)
End If
rownum = rownum + 1
Loop
End Sub
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.