tried to change my code but cant get it to work.

[Rob]

Hi I have been using the following code to try and
achieve the following -

*If the destination cell already has a value and the form
cell is blank then do not overwrite move to the next form
entry cell.

*If the form has a value, and the destination cell has a
value display the message asking whether or not to
replace.

*If the form has a value but the destination cell does
not then enter the value then move on to the next without
messagebox.

This is the code which works perfectly but asks if I
would like to replace a value with a blank if the form
cell is empty and the destination cell has a value.

Thanks a lot for your help. I really appreciate it.

Rob

Set Rng = shData.Range("E" & Application.Match(CLng
(Sh.Range("D6")), shData.Range("B:B"), 0))
Resp = vbYes
If Rng.Value <> "" Then
Resp = MsgBox("Digester 1 Temp Already Contains a value
of : " & Rng.Value & " " & Chr(176) & "C" & vbCr _
& "Would you like to change its value to : "
& Sh.Range("D8") & " " & Chr(176) & "C", Buttons:=vbYesNo)
End If
If Resp = vbYes Then
Rng.Value = Sh.Range("D8").Text
Else
End If

--- and this is the code i have tried to change to allow
me to take into consideration all of the 3 things at the
top of the page. Its almost as if when I tell it to goto
1 it does but then goes back up and runs through the rest
of the code too!!

Dim Rng As Range

Set Rng = shData.Range("E" & WorksheetFunction.Match
(Sh.Range("D6"), shData.Range("B:B"), 0))
Resp = vbYes
If Rng.Value = "" Then
GoTo 1
Else
End If
If Rng.Value <> "" Then
Resp = MsgBox("Digester 1 Temp Already Contains a value
of : " & Rng.Value & " " & Chr(176) & "C" & vbCr _
& "Would you like to change its value to : "
& Sh.Range("D8") & " " & Chr(176) & "C", Buttons:=vbYesNo)
End If
If Resp = vbYes Then
Rng.Value = Sh.Range("D8").Text
Else
1
End If

Set Rng = Nothing

 

[Dick Kusleika]

Hi I have been using the following code to try and
achieve the following -

*If the destination cell already has a value and the form
cell is blank then do not overwrite move to the next form
entry cell.

*If the form has a value, and the destination cell has a
value display the message asking whether or not to
replace.

*If the form has a value but the destination cell does
not then enter the value then move on to the next without
messagebox.

If IsEmpty(Rng.Value) Then
Rng.Value = Sh.Range("D8").Text
Else 'Range is not empty
If Not IsEmpty(Sh.Range("D8").Value) Then
Resp = MsgBox(etc..)

If Resp = vbYes Then
Rng.Value = sh.Range("D8").Text
End If
End If
End If

 

[Guest]

Thank you Dick the code works great

-----Original Message-----


If IsEmpty(Rng.Value) Then
Rng.Value = Sh.Range("D8").Text
Else 'Range is not empty
If Not IsEmpty(Sh.Range("D8").Value) Then
Resp = MsgBox(etc..)

If Resp = vbYes Then
Rng.Value = sh.Range("D8").Text
End If
End If
End If


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com




.