VBA How to Update the information to the correct Row?

[Panderz_GG]

Hello!


Table of Contents:

1. Situation (already working Makro's that are used
2. Question
3. Example

I'm currently working on a personal project that is supposed to help me at work.

Note: I'm not using UserForms

1. Situation:

I've programmed a Macro that lets me copy Values I put into the Range E2:E9 to a range starting with C13:J13 and then continuing to the next empty row downward while preventing Duplicates. So to put it simple it says = If value E2 already exists in column C then MsgBox "Value already exists!".

The code for that looks like this.

Sub NeuesKFZ()
 Dim sh As Worksheet, arr, lastErow As Long, matchCel As Range
 
 Set sh = ActiveSheet
 arr = sh.Range("E2:E9").Value
 lastErow = sh.Range("C" & sh.Rows.Count).End(xlUp).Row + 1
 
 If Range("E2") = "" Then
        MsgBox "Wählen Sie ein KFZ aus!"
        Range("E2").Select
    Exit Sub
End If
 
If lastErow < 13 Then lastErow = 13
 'check if the range has not been alredy copied:
 Set matchCel = sh.Range("C13:C" & lastErow - 1).Find(WHAT:=sh.Range("E2").Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
 If Not matchCel Is Nothing Then MsgBox sh.Range("E2").Value & " Existiert bereits in Zelle  " & matchCel.Address & "!": Exit Sub
 sh.Range("C" & lastErow).Resize(1, UBound(arr)).Value = Application.Transpose(arr)
 sh.Range("E2:E9").ClearContents
End Sub

Now I also have made a Makro in which I can pull up already stored data with E2 acting as the search criteria. In simple terms.

If E2 exist in C get Values of that row and copy/transpond them to E3:E9 (E2 not necessary because correct value already present).

The code looks something like this

Sub KFZAufrufen()
 Dim sh As Worksheet, arr, lastErow As Long, matchCel As Range  'Nötige Variablen
 
 Set sh = ActiveSheet                                           'Variabel Deklaration
 arr = sh.Range("E2:E9").Value                                  'Wenn in Zukunft mehr Daten aufgenommen werden sollen hier E10 verändern
 lastErow = sh.Range("C" & sh.Rows.Count).End(xlUp).Row + 1
 
 
 If Range("E2") = "" Then
        MsgBox "Wählen Sie ein KFZ aus!"                        'Zwingt den Nutzer ein KFZ aus der Dropdown in E2 zu wählen, sollte nicht verändert werden
        Range("E2").Select
    Exit Sub
End If
 
 
 If lastErow < 13 Then lastErow = 13
                                                                'Suche nach dem KFZ welches durch die Zelle E2 definiert wird
 Set matchCel = sh.Range("C13:C" & lastErow - 1).Find(WHAT:=sh.Range("E2").Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
 If Not matchCel Is Nothing Then
    MsgBox sh.Range("E2").Value & " wurde gefunden in " & matchCel.Address & "."
                                                                'Fahrzeugdaten aus der Tabelle in die in die Bearbeitung hoch holen
    sh.Range("E3:E9").Value = Application.Transpose(sh.Range(matchCel.Offset(0, 1), matchCel.Offset(0, 7)).Value)
    Exit Sub
End If
 sh.Range("C" & lastErow).Resize(1, UBound(arr)).Value = Application.Transpose(arr)
 sh.Range("E2:E9").ClearContents
End Sub

2. QUESTION:

How can I Update the retrieved Data in E3:E9 to that exact row I got the values from in the first place?

3. Example:

User puts in value y into E2.

Value y is found in C30.

Range D30:J30 is copied/transponded to Range E3:E9 (Again C30 is not needed because Value y is already present in E2).

User changes Value x in E3 to value z.

Value x is replaced with value z in D30 without creating a duplicate.


I really don't know how to do that.

 

[DanteAmor]

Hi and welcome to MrExcel.

Update the macro NeuesKFZ:

Sub NeuesKFZ()
  Dim sh As Worksheet, arr, lastErow As Long, matchCel As Range
  Dim opt As Variant
 
  Set sh = ActiveSheet
  arr = sh.Range("E2:E9").Value
  lastErow = sh.Range("C" & sh.Rows.Count).End(xlUp).Row + 1
 
  If Range("E2") = "" Then
    MsgBox "Wählen Sie ein KFZ aus!"
    Range("E2").Select
    Exit Sub
  End If
 
  If lastErow < 13 Then lastErow = 13
  'check if the range has not been alredy copied:
  Set matchCel = sh.Range("C13:C" & lastErow - 1).Find(WHAT:=sh.Range("E2").Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
  If Not matchCel Is Nothing Then
    opt = MsgBox("Existiert bereits in Zelle " & matchCel.Address & "!" & vbCr & _
          "Möchten Sie Daten aktualisieren?", vbYesNo)
    If opt = vbYes Then
      lastErow = matchCel.Row
    Else
      Exit Sub
    End If
  End If
 
  sh.Range("C" & lastErow).Resize(1, UBound(arr)).Value = Application.Transpose(arr)
  sh.Range("E2:E9").ClearContents
End Sub