Excel VBA Problem :(

[Claire1983]

Hi All,

I know what you thinking, I am a dumb blonde with no idea what I am doing... You are probably right.

My problem:

I have an excel spreadsheet with loads of data :( What I need to do is a VBA piece of code to go through all of Column A, and every time the product code changes, I need a new row to be inserted beneath... and then for the loop to continue until it gets to the last code.

e.g.

75.22.01
75.22.01
75.22.01
54.25.02
54.25.02
74.22.01
74.22.01

So I would like a row inserted every time the code changes. Can anybody help? Understand if you are all too busy. Many thanks, Claire :help

[Bob Phillips]

What do you think we are here for if it isn't to help out a dumb blonde with no idea of what they are doing :)



Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim iLastRow As Long
Dim i As Long

With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow - 1 To 1 Step -1
If .Cells(i, TEST_COLUMN).Value <> .Cells(i + 1, TEST_COLUMN).Value Then
.Rows(i + 1).Insert
End If
Next i
End With
End Sub

[Claire1983]

That is brilliant, you are very kind to help me and I much appreciate it :) I have got the hang of recording Macros but when it comes to writing them myself, I am awful. Ah well practise makes perfect... right? Thank you so much once again! :)

[Bob Phillips]

Well, recording macros is very useful, but very limited. It is useful to find out how a particular technique, but you have to know how to adapt it from there. All cells are hard-coded, so you need to add loops in there and control the loop, and it ALWAYS does unnecessary selects which are intrusive, especially if managing many sheets.

[Bob Phillips]

Also, in my code, notice how I start at the end and work my way back up. This is an important technique when inserting and deleting rows.

[Claire1983]

This going to seem very cheeky but you de seem extremely helpful and I guess if it is too much to ask, you dont have to help me :) If you can, and it does seem tricky, you will save me weeks of time.

Next question:

I have attached an example of the spreadsheet I am working on. I have filled in the three formulas I want to use in E3, F3 and G3. I need a script to follow your last one which will basically fill in these formula when there is a Part. However, I do not want these formula calculated for the first entry of each part number, as you can see I have a left a gap in my example at the top of my sheet. And I would like the blank rows kept blank... if you get me? lol

As I said this is very cheeky to ask, but if you could help me I will send lots of hugs and kisses your way :). :bow:

[Bob Phillips]

Just change the formula to

=IF(OR($A3="",$A2=""),"",(C3-C2)^2)
=IF(OR($A3="",$A2=""),"",(D3-D2)^2)
=IF(OR($A3="",$A2=""),"",SQRT(E3+F3))

and copy down.

[Claire1983]

Thank you once more, but I have many spreadsheets I need to do this to... is it possible to incorporate it into the VBA code? Or is that too tricky? Many thanks. x

[mdmackillop]

Sub AddFormulae()
With ActiveSheet.Columns("D:D").SpecialCells(xlCellTypeConstants, 1)
.Offset(, 1).FormulaR1C1 = "=(RC[-2]-R[-1]C[-2])*(RC[-2]-R[-1]C[-2])"
.Offset(, 2).FormulaR1C1 = "=(RC[-2]-R[-1]C[-2])*(RC[-2]-R[-1]C[-2])"
.Offset(, 3).FormulaR1C1 = "=SQRT(RC[-2]+RC[-1])"
End With
ActiveSheet.Range("E2:G2").ClearContents
End Sub

[Bob Phillips]

That puts the formula in row 5, which is the first entry of a part number, which is not wanted.

I offer



Sub AddFormulae()
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("E3").Resize(iLastRow - 2).Formula = "=IF(OR($A3="""",$A2=""""),"""",($C3-$C2)^2)"
.Range("F3").Resize(iLastRow - 2).Formula = "=IF(OR($A3="""",$A2=""""),"""",($D3-$D2)^2)"
.Range("G3").Resize(iLastRow - 2).Formula = "=IF(OR($A3="""",$A2=""""),"""",SQRT($E3+$F3))"
End With
End Sub

[Claire1983]

Hi all, thank you very much for all your helps... it works now :) if you ever need your hair straightened then just let me know!!:)