Updating(Amending) cells in data

[MIAnalyst]

I have a form that I need the user to be able to search to see if the data is already there, if it is then the user will need to amend pre-existing data.

I have no idea where to start, I can build a form to input new data but not to amend certain cells in existing data.

Can anyone help me please.

Thanks in advance

 

[SkipVought]

Hi,

Is the Excel?

When you say FORM, it implies something very specific in ths forum; a UserForm Control. Is that what you have?

Please be Clear and Specific. Give some examples of what you mean by, "amend pre-existing data"

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[MIAnalyst]

Sorry - been having a very long day with lots of erros and debugs and getting nowhere.

I have created a spreadsheet that has a UserForm which enables a person to imput data. First the macro searches the data that has already been inputted to find if the trainee name is already there. If it is, a AmendData userform appears to allow the person to update certain cells. If the data is not there, another form (AddTrainee) appears to allow the person to input a new trainee. I have the AddTrainee UserForm working perfectly thanks to everyone's help here but have no idea how to create the second UserForm.

Scenario 1 -

Trainee1 is already in the Backing Data page showing 25%, 35% and 45%
The trainer needs to change these percentages to 28%, 38% and 48%

Hope this is a bit clearer

 

[SkipVought]

Check out the Find method to locate the cell containing the data, for instance is you're looking in column A, where sValueToFind is what you're looking for...

dim foundCell as range

set foundCell = Sheets("YourSheet").Columns(1).Find(sValueToFind)
if foundCell = nothing then
  'not found - add a new row.
else
  'found it - now make ammends.
end if

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[MIAnalyst]

Thanks very much but I do apologise if I'm being very dense here (they're sending me on training next month)

I already have the coding that finds it:

Sub GetData()

'prompt for trainee & weekending date

Dim TraineeName As String
Dim WeekEndingDate As Date
Dim rowcount

TraineeName = InputBox("Please enter trainee name")
WeekEndingDate = InputBox("Please enter week ending date - dd/mm/yyyy")

'lookup based on prompt results

rowcount = Sheet2.Rows.Count
If rowcount > 2500 Then rowcount = 2500
For x = 2 To rowcount

     If Sheets(2).Cells(x, 1) = TraineeName And Sheets(2).Cells(x, 5) = WeekEndingDate Then
     
'if found then start up amend data form
 
 AmendData.Show
    
    Exit Sub
    End If
      
Next x

'if not found start up TraineeDetails form
    TraineeDetails.Show

End Sub

So I'm a bit confused

 

[SkipVought]

OK, so you have already found the ROW that you want to ammend.

What data in the row is amenable to amending? You cannot amend EVERY column.

Please post an example of the ROW.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[MIAnalyst]

Trainee %Achieved1 %Target1 %Achieved2 %Target2
Trainee1 25% 25% 35% 35%

 

[MIAnalyst]

Sorry - pressed submit instead of preview

Trainee %Achieved1 %Target1 %Achieved2 %Target2
Trainee1 25% 25% 35% 35%

The achieved percentages will be the ones that need amending

 

[xlbo]

All you need to do is read the data in the column / row you want into text boxes on the form:

e.g. Textbox1.text = range("A1").text

User can then amend textboxes and have a "submit" button that puts the data back on the spreadsheet e.g.
Range("A1").value = TExtbox1.TExt

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Are these two rows adjacent?

Are the headings at the TOP os the data?

Is all the data contiguous?

Is all the data in columns A thru E?

Where is the week ending date?


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}