PDA

View Full Version : how to find the value and replace with diff value



rrosa1
07-10-2010, 01:19 PM
hi
how to find the value in column G and replace with diff value and
also change value in anther 3 column but same row
column G with input value is working in this code but not change the value of column
H = c*.88,
I =c*0.12,
J=sum(H:I)
on same row which changed by input value.

Sub changevalue2()
Dim r As Range, rng As Range, r1 As Range, r2 As Range, s As String, c As String ,r3 As Range



Set r = Range("G5", Range("G580").End(xlUp))

r.Offset(0, 8).ClearContents

s = InputBox("What to find and change?")
c = InputBox("change with")

With Range("G5")
.AutoFilter Field:=7, Criteria1:=s

On Error Resume Next

Set rng = r.Cells.SpecialCells(xlCellTypeVisible) 'if no items found

If rng Is Nothing Then
MsgBox "No records found"
.AutoFilter
Exit Sub
End If
r = c '<----- its working perfectly as per my need
r1.Offset(0, 8) = c * 0.88 '<----- not working
r2.Offset(0, 9) = c * 0.12 '<----- not working
r3.Offset(0, 10) = c '<----- not working
.AutoFilter
End With


End Sub

thanks for help

GTO
07-10-2010, 02:28 PM
Not tested, but in a copy of your wb, try ...
Set rng = r.Cells.SpecialCells(xlCellTypeVisible) 'if no items found
On Error GoTo 0

I believe you are masking errors, by leaving the error checking shut off.

I do not think that r1, r2, or r3 are valid ranges, least from what I could see.

Mark

rrosa1
07-10-2010, 02:42 PM
hi Mark
thanks for looking .i am not the authur of this code i copy from anther thread
and changing to suit my need. so i don't know how to masking error or how to define valid ranges so pl help
the code is changing the value of column G as it find the input value with input value to change but also i need to change the value of column H,I,J
as H = c*0.88 ,I = c*0.12 and J = c on the same row
thanks for help

GTO
07-10-2010, 02:57 PM
Please look again at Post# 2 as I fixed a rather dumb error on my part.

Put an On Error GoTo 0 in there as indicated, and see if the code does not trip on r1 etc.

rrosa1
07-10-2010, 03:12 PM
hi Mark
it run in to error 91 "object variable or with block variable not set" on the line

r1.Offset(0, 8) = c * 0.88
so i add this one

Set r1 = Range("H5", Range("H580").End(xlUp))

r1.Offset(0, 8) = c * 0.88
now it change the value of column H but with same as column G not as required as c*.88

GTO
07-10-2010, 03:23 PM
Can you post the workbook in .xls format?

rrosa1
07-10-2010, 03:38 PM
hi Mark here is the sample wb

GTO
07-10-2010, 06:06 PM
Greetings rrosa,

I am afraid that I am not following what is supposed to be happening whatsoever.

Take a look at the picture; here is what seems to be happening to me:


Option Explicit

Sub changevalue2()
Dim r As Range, rng As Range, r1 As Range, r2 As Range, s As String, c As String, r3 As Range

'// First we are setting a range from G5 to the last cell with data ABOVE G580. //
'// I mention above, as normally we would just run up from the bottom of the sheet. //
'// Is there a reason that we started upward from row 580? //
Set r = Range("G5", Range("G580").End(xlUp))

'// Then we get two text values to use //
s = InputBox("What to find and change?")
c = InputBox("change with")

'// This is where I at least, get very confused. Row 5 has data in it, albeit //
'// currently all zeros, and we are using it for a header row for the filter. As //
'// long as there is no actual needed data, okay...? //
'// Then we are enacting an autofilter on G5. This means it only filters to row 8, //
'// as that's the last bit of contiguous data, and excel is guessing. //
'// Further - we are filtering by Column M. Column M has a mix of date/time //
'// values, like '6/2/2010 9:33:16 AM' as well as non-date/time text values, such as//
'// '5/19.02/2010 9:16:08 AM' ?!?! //
'// If by chance the user correctly types in '6/2/2010 9:33', the displayed value, //
'// then the filter leaves only row 8 of the filtered area showing... //
With Range("G5")
.AutoFilter Field:=7, Criteria1:=s

'// ...which we are evidently using for this. The trouble is that the range 'r' //
'// is only slightly affected, as the filter was only applied to a smidgeon of the //
'// rows. I do not imagine that this is what you were looking to effect. //
On Error Resume Next
Set rng = r.Cells.SpecialCells(xlCellTypeVisible) 'if no items found
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No records found"
.AutoFilter
Exit Sub
End If

'// What??? Look at the picture, see this did? //
r = c
'// This is where I stopped. I am not one to harp about naming conventions, as //
'// I tend to get a bit sloppy with longs. That said, it seems pretty easy //
'// to pick out what 'x' and 'y' represent when someone is transposing an array //
'// for instance. But 'r = c', wherein 'r' represents a range object and 'c' //
'// is just some value the user entered in the inputbox makes it awfully //
'// tough to decipher the code. //
'// Might I suggest that you would be making it easier not only on anyone //
'// wishing to help, but yourself as well, if you try and give stuff //
'// meaningful names. //

' ...remainder of code...
In closing, I would also suggest you try doing as I did in the picture; that is, reduce VBIDE and step thru your code so you can see where things are happening.

Well, I hope that's a bit of help at least. Have a great day,

Mark