PDA

View Full Version : loop to delete the row and replace the value in cell having problem



rrosa1
05-01-2010, 04:58 PM
hi
actual i have "Today"sheet in WB i want to create new sheet in same WB name"Yesterday" than "Today" sheet data need to adjusting according the value in Column 'B' if B8 onward row have "1" in cell delete the same row then after finishing delete in all row.i need to change the data in column"B" with Column "R" and Column "E" with column"S" value. i hope i am explaining the way it can easily understandable.
for reference i attached the test WB it doe's create the "Yesterday" sheet and delete the last row from "Today"sheet but Not Deleting all Raw with the value "1" in Column "B" and not replacing the value for column"B" with Column "R" and Column "E" with column"S" value.of same row.
also input form not entering data in "list"which some time do and some time don't enter the data in list
i am new to VB i am a Lerner. this code also from this forum help.i hope folks will help me again.
thanks for any help

GTO
05-01-2010, 08:22 PM
Hi rrosa1,


...i have "Today"sheet in WB i want to create new sheet in same WB name"Yesterday"
Okay so far.

than "Today" sheet data need to adjusting according the value in Column 'B' if B8 onward row have "1" in cell delete the same row

then after finishing delete in all row.
I am afraid I am quite lost from "then after finishing, delete in all row." What do you mean, delete in all row(s)?

Might I suggest using bullets/numbering so that your explanation is broken up into logical steps? Also, if it is too late to edit your attachment at post #1, then maybe include the example wb, but this time, add a sheet where you have re-arranged what the "Today" sheet should look like after being updated. You could name it "Today Updated" so that we can see what the 'before' and 'after' look like. I think this would help make it clearer, so that we could be of better help.

Thank you,

Mark

rrosa1
05-01-2010, 10:02 PM
hi Mark
here u can have a pic of flow in PDF is that ok? also if u can check the atteched Xls file u can have better idea what i am traying to do
thanks

Bob Phillips
05-02-2010, 03:23 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With Worksheets("Present Sheet")

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 6 Step -1

If .Cells(i, "R").Value2 = 0 Then

.Rows(i).Delete
End If
Next i
End With
End Sub

rrosa1
05-02-2010, 06:09 AM
hi xld
i change the code as yours and it give the error msg
Run time error 1004
Application-defined or object-defiend error

my old code was as in commented below

ws1.Unprotect Password:=""


Const TEST_COLUMN As String = "1" '<=== change to suit

Dim LastRow As Long

With ws1

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 8 Step -1

If .Cells(i, "B").Value2 = 1 Then

.Rows(i).Delete
End If
Next i
End With




'day1 = "1"
'With ws1
' For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 8 Step -1
' If InStr(1, .Cells(i, 2).Value, day1) = 1 Then
' .Cells(i, 2).EntireRow.Delete
' Exit For
' End If
' Next i
'
'End With

With ws1
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 8 Step -1
If InStr(1, .Cells(i, 2).Value, day1) > 1 Then
.Cells(i, 2).Value = .Cells(i, 18).Value
.Cells(i, 5).Value = .Cells(i, 19).Value
Exit For
End If
Next i

.Range("L2").Value = " Last Report Generated"
.Range("L3").Value = Now()

End With
thanks for help

Bob Phillips
05-02-2010, 06:13 AM
Just take out all of the code and replace it with what I gave you.

rrosa1
05-02-2010, 06:23 AM
hi
it still give the same error msg
but my old code work to delete the row since its was not looping though the all row but now i find and u can see the code with commented line it was stooping the loop

day1 = "1"
With ws1
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 8 Step -1
If InStr(1, .Cells(i, 2).Value, day1) = 1 Then
.Cells(i, 2).EntireRow.Delete
'Exit For
End If
Next i

End With

but any idea how can this code make to work
this code suppose to replace the value for column2 with column18 and
column5 with column19

With ws1
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 8 Step -1
If InStr(1, .Cells(i, 2).Value, day1) > 1 Then
.Cells(i, 2).Value = .Cells(i, 18).Value
.Cells(i, 5).Value = .Cells(i, 19).Value

End If
Next i

.Range("L2").Value = " Last Report Generated"
.Range("L3").Value = Now()

End With

Bob Phillips
05-02-2010, 07:45 AM
Where are the sub signature code?

rrosa1
05-02-2010, 08:28 AM
hi xld
i am new to VB code and i don't know what is your asking
"Where are the sub signature code?"
but playing with the code i make the second loop also working as the code as below

With ws1
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 8 Step -1

.Cells(i, 2).Value = .Cells(i, 18).Value
.Cells(i, 5).Value = .Cells(i, 19).Value

Next i

.Range("L2").Value = " Last Report Generated"
.Range("L3").Value = Now()

End With

i am posting this code for some one like me copy paste the code and learn the coding

but u can see my WB in attachment
one last thing i have a "list" in Today sheet where data come from the userform as i enter the data data suppose to go in the "list" since in some column i have with Fx to calculate but some time as i enter the data in userform data go out side the "list" row
is there some thing i have to do for consistently adding the data in "list" ?
i hope i am not giving pain asking lot's of question.
thanks for looking my problem.

rrosa1
05-02-2010, 08:47 AM
Hi mdmackillop
i know u was reading my post this morning .in past u help me with my problem so thanks .i like your style of helping in this forum u give the direction not solution so some one can learn. by the way i like your Avatar.

Hi xld
u also help me in past and give the direction not solution so like my kind of folks can learn so thanks

also don't forget to give the direction for the "list" data entry behaving weird.