Please help! VBA: Replacing values in a sheet

mpvmvp

New Member
Joined
Dec 5, 2005
Messages
6
Ok, so I have a sheet that takes data from this form and adds it to a list of items. Sometimes the status of items change and the form is used to update the list. however, the form just enters a duplicate copy of the item without removing the old one...how can I do this? here is my code:

ActiveWorkbook.Sheets("Camera Report Form").Activate
Range("A7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 3) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value

The list is sorted by the value "txtName.Value"
I tried entering this code:

If Selection.Value = txtName.Value Then
ActiveCell.ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
ActiveCell.Offset(0, 3).ClearContents
End If

but that didn't seem to work...can anyone help??? Thanks so much
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
For the benefit of curious types...this was my pass at improvement...can probably refined even further, but it works pretty well for the OP

Code:
Dim f As Integer 'font
Dim cm As Integer 'camera number
Dim co As Integer 'color index
Dim s As Integer 'score selection
Dim d As Date 'Date last changed in column E of main sheet
Dim v As String 'Visibility chosen
d = Now() 'captures the date and time of click
cm = Val(txtCamera.Text)
s = Val(cboScore.Text) 'converts the selection to a value
v = cboVisibility.Text 'stores the selected visibility as a string variable
fov = txtFieldofView.Text 'stores the field of view text
Select Case s
'fill font color(f) and interior color(co) variables based on score selected(s)
 Case 1
   f = 1
   co = 3
 Case 2
   f = 2
   co = 9
 Case 3
    f = 1
   co = 6
 Case 4
   f = 2
   co = 10
 Case 5
    f = 1
   co = 4
End Select

If chkBroken = True Then 'set colors for when this check box is clicked
   f = 2
   co = 1
End If

If chkpriority = True Then 'set colors for when this box is clicked
   f = 6
   co = 1
End If

PassData cm, s, co, f, d, v, fov
'pass data to subroutine
End Sub



Sub PassData(ByVal iCam As Integer, iScore As Integer, iColor As Integer, iFont As Integer, iDate As Date, iVisibility As String, iFov)
Dim MySheet As Worksheet
Dim rngFound As Range
Dim sRange As Range
Dim SortRange As Range
Dim lastrow As Long
Dim x As Integer
Dim y As Integer

Set MySheet = ActiveSheet 'sets the current sheet

'First delete match from Broken sheet to camera number
If chkBroken = False Then
  With Sheets("Broken")
  Set rngFound = Range("A:A").Find(What:=iCam, LookIn:=xlValues, LookAt:=xlWhole)
  If Not rngFound Is Nothing Then rngFound.EntireRow.delete
'deletes a broken camera if the camera number matches and is not broken anymore  
End With
End If

y = 2
If chkBroken = True Then 'if Broken was selected set score at 0
iScore = 0 'no score for brokens
y = 1 
End If


For x = 1 To 3 Step y 'begins a loop to cycle the sheets skips sheet 2 (broken) if broken box was clicked

  
  If iScore = 0 Then iVisibility = "BROKEN" 'changes the text to broken
    Sheets(x).Activate
    With ActiveSheet
    Set sRange = Range(Cells(6, 1), Cells(6, 1).End(xlDown))
    Set rngFound = Nothing 'empty the search range result variable
    
    'look in column "A" for iCam (the camera selected) if exists..do the following:
        Set rngFound = sRange.Find(What:=iCam, LookIn:=xlValues, _
                                            LookAt:=xlWhole)
    
     If rngFound Is Nothing Then 'if no match then put in bottom row
     lastrow = Cells(65536, 1).End(xlUp).Row + 1
     Else
     lastrow = rngFound.Row 'if match than use the matching camera number row
     End If
     
     Cells(lastrow, 1) = iCam * 1
     Cells(lastrow, 2) = iScore
     Cells(lastrow, 3) = iVisibility
     Cells(lastrow, 5) = iDate
     Cells(lastrow, 4) = iFov
     
     Range(Cells(lastrow, 1), Cells(lastrow, 5)).Select
     With Selection.Font
       .ColorIndex = iFont
       .Bold = True
     End With
     Selection.Interior.ColorIndex = iColor
     Selection.HorizontalAlignment = xlCenter
         
      If ActiveSheet.Name <> "ByScore" Then
       Set SortRange = Range(Cells(6, 1), Cells(6, 1).End(xlDown).Offset(0, 5))
       'Sort by Camera number
       SortRange.Select
      Selection.Sort Key1:=Range("A7"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
      End If
      
      If ActiveSheet.Name = "ByScore" Then
       Set SortRange = Range(Cells(6, 1), Cells(6, 1).End(xlDown).Offset(0, 5))
       'Sort by Camera number
       SortRange.Select
      Selection.Sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
      End If
      
    End With
Next x

For Each ws In Worksheets
ws.Protect
Next ws


MySheet.Activate 'Activate Original Sheet
Range("A7").Select
[/code]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,171,652
Messages
5,876,705
Members
433,206
Latest member
james_y

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top