Solved: Adding rows with preconditioned cells

[maraippo]

Hi..

I tried to make a macro to place certain data in same row for every worksheet

what i try is, i use if then..end if function to define data in the cell is equal to its designated. if its true it will just move on to the next cell, if wrong it will add blank row above...

here is code that i tried, but not working...

Sub add_empty_row()

'this macro will adjust the row for appropriate bin allocation

Range("C18").Select
If "C18" = Bin1 Then
Range("C19").Select
Else
ActiveCell.EntireRow.Insert shift:=xlUp
End If

Range("C19").Select
If "C25" = Bin7 Then
Range("C26").Select
Else
ActiveCell.EntireRow.Insert shift:=xlUp
End If

End Sub
I do this for later process where i need to sum data in same cell in all worksheet to put formula it in another worksheet

The formula will go like this:

='1#'!D19+'1# (2)'!D19+'1# (3)'!D19+'1# (4)'!D19+'1# (5)'!D19+'1# (6)'!D19
Or any of you have ideas on how to make it better. Total row for data will be 36 and have 7 column.

I need to do similar formula for each cell in the table containing particular data.


Thanks in advance :)

[maraippo]

I've found a way to the inserting row with this method:

Sub add_empty_row()

'this macro will adjust the row for appropriate bin allocation


With Range("C18")
If .Value = "Bin1" Then
Range("C19").Select
Else
.EntireRow.Insert
Range("C19").Select
End If
End With

With Range("C19")
If .Value = "Bin2" Then
Range("C20").Select
Else
.EntireRow.Insert
Range("C20").Select
End If
End With
.
.
.
.
With Range("Cn")
If .Value = "Bin n" Then
Range("Cn+1").Select
Else
.EntireRow.Insert
Range("Cn+1").Select
End If
End With

End sub

At the moment i just add each command for different line. Does anyone have any idea on how to make the code simpler?

[mdmackillop]

Sub Test()
Dim i As Long
For i = 1 To 50
With Range("C" & 17 + i)
If .Value = "Bin" & i Then
Range("C" & 18 + i).Select
Else
.EntireRow.Insert
Range("C" & 18 + i).Select
End If
End With
Next i
End Sub


Depending on your data, this may work better in reverse, so change the loop to

For i = 50 to 1 step -1

[maraippo]

Thanks mdmackillop (http://vbaexpress.com/forum/member.php?u=87) for replying.

I've tried your code however error comes out



Compile error
"Next withour For"



I've faced similar problem before but don't know whats the reason. I would like to know about this too.. :)

Thank you :)

[Bob Phillips]

Where MD's code says End With, it should be Next i

[maraippo]

thanks..the prob solved :)

Regarding inserting the formula issue, i tried to record the process but when i tried to run it back

it shows "application defined or object defined error"

the code recorded is this:

Sub collect_formula()
'
' collect_formula Macro
' Macro recorded 7/3/2008 by Trainee
'

'
ActiveCell.FormulaR1C1 = _
"='1#'!R[13]C[1]+'1# (2)'!R[13]C[1]+'1# (3)'!R[13]C[1]+'1# (4)'!R[13]C[1]+'1# (5)'!R[13]C[1]+'1# (6)'!R[13]C[1]+'1# (7)'!R[13]C[1]+'1# (8)'!R[13]C[1]+'1# (9)'!R[13]C[1]+'1# (10)'!R[13]C[1]+'1# (11)'!R[13]C[1]+'1# (12)'!R[13]C[1] '1# (13)'!"
Range("C7").Select
End Sub

But the formula i want to do is actually like this:


='s1#1'!B19+'s1#2'!B19+'s1#3'!B19+'s1#4'!B19+'s1#5'!B19+'s1#6'!B19+'s1#07'! B19+'s1#8'!B19+'s1#9'!B19+'s1#10'!B19+'s1#11'!B19+'s1#12'!B19+'s1#13'!B19+' s1#14'!B19+'s1#15'!B19+'s1#16'!B19+'s1#17'!B19+'s1#18'!B19+'s1#19'!B19+'s1# 20'!B19+'s1#21'!B19+'s1#22'!B19+'s1#23'!B19+'s1#24'!B19+'s1#25'!B19

Is the macro has limited amount of sheets that can be entered? any ideas? :-/

Thanks

[Bob Phillips]

I would have thought that you want something like



ActiveCell.FormulaR1C1 = "=SUM('S1#1:S1#24'!R[13]C[1])"


but it gives me a very strange result

[maraippo]

Yes...i would like to have a code to add value in same column;eg C12 in every sheets...like the one you did.

Hmm...what do you mean by "very strange result" when you run the code?

First i tried by recording the macro. However,error pop-out when i try to run the recorded macro again.

As my total sheet is 25, the macro only get recorded until the 13th sheet for unknown reason.

I will try your code.

Thanks xld for replying :)

i'll update with the result later.

[maraippo]

unfortunately the sum doesn't work...

it shows #REF! in the cell

I have 7 column and 36 row of data that need to be organize into one seperated single sheet. This new sheet will add up value from all the 25 sheets.

here i attach one of the data file...original format is in .smy but i change the extension to .doc for uploading purpose...

each bin represents a row...

not numbered bin is replaced by a blank row so that number of row for each worksheet is similar with each other..

my objective now is to be able to add each cells from all the sheet to a separated sheet...

hmm...is this impossible? :-/

well, i try not to give up as long as there's hope...haha


thank you in advance :)

[maraippo]

I tried recording macro for few times...

The recording method works fine up to the 6th or 7th sheets. And the recorded macro can be run if the recorded is just up to 7th sheet.

More than seven it cannot run.

But when I try to add more than 13 sheet the macro only recorded up to the 13th sheet.

Hmm...does anyone face similar problem?

edit: i tried the sum formula...kinda solve the prob :)

thanks for helping xld and madmackillcop :)