[2013] VBA button - Copy values from one sheet to another sheet, in all rows containing a specific reference.

[Darkoro]

I have a large database of information in Excel (no other software choice unfortunately) in which others want to insert data into, without worrying about entering the data into the wrong cells and dirtying up the data source sheet. Said others are not Excel-savvy, and as such I need to prepare a method of data entry for these other users to be able to addend their results without messing up the source sheet.

I've come up with the idea of the form in the below image, where the user would input the "Reference" (in the example, test reference 4), and the current values are shown using VLOOKUP. The user would then input the relevant new values in the bottom row, and then press the button "Save new Values" to copy those values over to the source file.

The other issue I have, as shown in the below image, is that there can be single or multiple rows referring to a single reference (in my actual database, the largest number of rows for a single reference reaches 49). Each row for test reference 4 would have to be updated with a single button push.

I know that this should be possible with VBA, but I haven't been able to piecemeal any code together for the button based on the various things I've seen online. Does anyone have a VBA code snippet for me that can make this work?

 

[Kaper]

Assuming your form is on a separate sheet, where the name of

reference is in A3 and nev values to be written in C6:F6
and your main data is in Sheet called Databasesheet, the references in column A, the extra values in columns 2..5 to right from A (C:F too)   

the code assigned to the button could look like:

Sub test()
Dim rng As Range, firstaddress As String
With Sheets("Databasesheet")
  Set rng = .Columns("A:A").Find(what:=Range("A3"), lookat:=xlWhole, LookIn:=xlValues)
  If rng Is Nothing Then
    MsgBox "Oooops, haven't found the value", vbCritical
  Else
    firstaddress = rng.Address
    Do
      rng.Offset(0, 2).Value = Range("C6").Value
      rng.Offset(0, 3).Value = Range("D6").Value
      rng.Offset(0, 4).Value = Range("E6").Value
      rng.Offset(0, 5).Value = Range("F6").Value
      Set rng = .Columns("A:A").FindNext(after:=rng)
    Loop Until rng.Address = firstaddress
  End If
End With

Two "side comments"
- In VBA such form as presented in the first screenshot could be nicely prepared as userform being easier to maintain, less prone to "user invention" etc
- as you have repeating parts of the records (sometimes as many times as 49) it could be a good idea to have two tables one having only reference and "other data" and the second where every reference with all fill columns 1..4 is stored only once. And the use for instance Vlookup (or any other tool to follow the relation) to add these 4 values when particular ""otherdata" element is used.


End Sub

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Darkoro]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I appreciate the input, but the specific security settings at work prevent me from installing any add-ons or uploading any Excel files to any sort of file sharing website. I uploaded what I could, how I could. The fact that the formulas in the cells are written in a non-english version of Excel also do not help.

Assuming your form is on a separate sheet, where the name of

reference is in A3 and nev values to be written in C6:F6
and your main data is in Sheet called Databasesheet, the references in column A, the extra values in columns 2..5 to right from A (C:F too)  

the code assigned to the button could look like:

Sub test()
Dim rng As Range, firstaddress As String
With Sheets("Databasesheet")
  Set rng = .Columns("A:A").Find(what:=Range("A3"), lookat:=xlWhole, LookIn:=xlValues)
  If rng Is Nothing Then
    MsgBox "Oooops, haven't found the value", vbCritical
  Else
    firstaddress = rng.Address
    Do
      rng.Offset(0, 2).Value = Range("C6").Value
      rng.Offset(0, 3).Value = Range("D6").Value
      rng.Offset(0, 4).Value = Range("E6").Value
      rng.Offset(0, 5).Value = Range("F6").Value
      Set rng = .Columns("A:A").FindNext(after:=rng)
    Loop Until rng.Address = firstaddress
  End If
End With

Two "side comments"
- In VBA such form as presented in the first screenshot could be nicely prepared as userform being easier to maintain, less prone to "user invention" etc
- as you have repeating parts of the records (sometimes as many times as 49) it could be a good idea to have two tables one having only reference and "other data" and the second where every reference with all fill columns 1..4 is stored only once. And the use for instance Vlookup (or any other tool to follow the relation) to add these 4 values when particular ""otherdata" element is used.


End Sub

Thanks for this, I'll give it a try tomorrow and report back.

 

[Kaper]

non-english version of Excel also do not help

In this case shared (on public webserver) workbook is a real help. Because everyone opens it in the installed excel and the formulas are visible in the version installed. So if I publish a file with formula

=JEŻELI(A1=100;"hundred";"other value")

someone opening this file in US version of excel will see

=IF(A1=100,"hundred","other value")

Note that arguiment separators will be also different, not only the name of the function.
But going back to merit - Let's see how the code works. I've tested it with a sample file prepared the way i described in a part "Let's assume that ..."

 

[Darkoro]

But going back to merit - Let's see how the code works. I've tested it with a sample file prepared the way i described in a part "Let's assume that ..."

Well, I expected do it the next day, but then I had to go on sick leave the day after posting. But I'm back now and can confirm that the code works, at least in my example as shown. I'm going to attempt to make it work on the main file now, but thank you so much for the code snippet!

 

[Kaper]

Glad to hear it works on the sample file.

So we are waiting for the tests in real life