I need some help with an excel VBA function.
I have data that looks like this
ColA ColB
a123 a123
a124 a124
a127 a126
a128 a127
.... ....
I want to c开发者_JAVA百科ompare the contents of ColA and ColB, Where the contents are different I want to insert a blank cell into column A. So the result will look like:
ColA ColB
a123 a123
a124 a124
a126
a127 a127
.... ....
Any suggestions as to how I could do this in Excel.
Thanks in advance
UPDATED
I tried the method below with the following code to insert the cell and it is working fine, I now realize when I run it that I need some more functionality though.
first_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
if the value in ColA with the "a" removed is less than the value in ColB with the "a" removed I want to insert the cell in in ColA and I also need to insert a cell in the same position in ColC (contains other data). If ColB has a larger value I want to insert the cell in ColB only. I think I know what to do inside the If statement but I'm not sure how to construct the IF. Here is what I am thinking
Set other_col = Range("C1:C100")
//if substring of ColA > substring of ColB
first_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
other_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
//else
second_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
Your comparison statement could look something like this:
Sub Expand()
Dim first_col As Range, cell As Range
Dim row As Integer
Set first_col = Range("A2:A100")
For Each cell In first_col
If Right(cell.Value, 3) < Right(cell.Offset(0, 1).Value, 3) Then
' Shift in here
End If
Next cell
End Sub
The "Right" function looks at the number of characters from the right as specified by the second parameter.
One other note is that code sections like this that use the .select method:
first_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
Can be truncated like this:
first_col.cells(row, 1).insert shift:=xlDown
The "select" and "selection" are leftovers from the macro recorder and usually aren't used in VBA code.
I don't know the exact VBA code to insert a row, but you can find that by recording a macro. Here is the rest of the code (to loop through the columns and do the comparison):
Sub Expand()
Dim first_col as Range
Dim second_col as Range
Dim row as Integer
Set first_col = Range("A2:A100")
Set second_col = Range("B2:B100")
For row = 1 To second_col.Rows.Count
If first_col.Cells(Row, 1).Value <> second_col.Cells(Row, 1).Value Then
'// code to insert the row'
End If
Next row
End Sub
i record a macro by selecting some cells and insert and i tried by creating my own sub and it s work
Range("c16:e16").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
edit note this code cause unmerg to merged cells
精彩评论