Editing something via macro?

Antonow

New Member
Joined
Aug 19, 2015
Messages
39
Hello everyone, It´s been a long time since i´ve been here! and i need your help one more time.

I´m developing a spreadsheet where i have to input something like the following.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mold[/TD]
[TD]Model[/TD]
[TD]Comp 1[/TD]
[TD]Quant 1[/TD]
[TD]Comp 2[/TD]
[TD]Quant 2[/TD]
[TD]Comp 3[/TD]
[TD]Quant 3[/TD]
[TD]Comp 4[/TD]
[TD]Quant 4[/TD]
[TD]Status[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have 2 worksheets, one labeled Input and another one labeled Vorlagen.

I managed to make the input macro, where it gets the information from Input, places it in Vorlagen and clear the information from the input worksheet, here is the code:
Code:
Sub Vorlagen_Input()

    'Sheets("Input").Unprotect Password:="Verboten"
    'Sheets("Vorlagen").Unprotect Password:="Verboten"


    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim NextRow As Long


    Set ws1 = ThisWorkbook.Worksheets("Input")
    Set ws2 = ThisWorkbook.Worksheets("Vorlagen")


    With ws2
    
        NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        .Cells(NextRow, "B") = ws1.Cells(4, "B").Value  ' Schimmel
        .Cells(NextRow, "C") = ws1.Cells(4, "C").Value  ' Modell
        .Cells(NextRow, "D") = ws1.Cells(4, "D").Value  ' Komponente 1
        .Cells(NextRow, "E") = ws1.Cells(4, "E").Value  ' Anzahl 1
        .Cells(NextRow, "F") = ws1.Cells(4, "F").Value  ' Komponente 2
        .Cells(NextRow, "G") = ws1.Cells(4, "G").Value  ' Anzahl 2
        .Cells(NextRow, "H") = ws1.Cells(4, "H").Value  ' Komponente 3
        .Cells(NextRow, "I") = ws1.Cells(4, "I").Value  ' Anzahl 3
        .Cells(NextRow, "J") = ws1.Cells(4, "J").Value  ' Komponente 4
        .Cells(NextRow, "K") = ws1.Cells(4, "K").Value  ' Anzahl 4
        .Cells(NextRow, "L") = ws1.Cells(4, "L").Value  ' Status
        .Cells(NextRow, "M") = ws1.Cells(4, "M").Value  ' Daten


    'Sheets("Input").Protect Password:="Verboten"
    'Sheets("Vorlagen").Protect Password:="Verboten
    
    End With
    
    Range("B4:M4").Select
    Selection.ClearContents
    Range("B4").Select

What i'd like to do is, have a worksheet or a button in the Input worksheet, that when you select a Mold from a dropdown list, it gets the information of that one and if you need to change lets say.. its status, you change and press "Update" and it will update it in the Vorlagen worksheet. I've been hitting my head against the keyboard for hours and I can't seem to make it work. Can you guys help?

Btw, i'm sorry for any grammatical error.. It's been a while since i've used English. :)
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
If ive read correctly you need this part:

Code:
With ws2

to be dependant on the dropdown cell value? Is that correct?
 
Upvote 0
Ok. Change the first part to the sheet and cell reference of your dropdown and change 'update' to whatever you say in the cell then you can set your variable like this:

Code:
If Sheets("Sheet1").Range("A1").Value = "update" Then
    NextRow = Application.Match(ws1.Cells(4, "C").Value, ws2.Columns("C"), 0)
Else
    NextRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row + 1
End If

If IsError(NextRow) Then
    MsgBox "There is no match found. You cannot update"
    Exit Sub
End If
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

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
Back
Top