Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Replacing values with a twist.

    Ok, I have this script that I have been working on and having some people help me with tweak, but I need help because I think I hit their limit of knowledge, and I don't know where to go from here...

    I'm making the values in the attachment it copies simple for the sake of conversation, but this is going to be used on a multiple field form created in excel.

    Code:
    Option Explicit
    
    Sub CopyValues()
        Dim i As Integer
    
        'Simple Form
        Dim wsInt As Worksheet
        Dim wsNDA As Worksheet
    
        'Copy Ranges Variable
        Dim c As Variant
    
        'Paste Ranges Variable
        Dim p As Range
    
        'Setting Sheet
        Set wsInt = Sheets("Form")
        Set wsNDA = Sheets("Data")
        Set p = wsInt.Range("A14")
    
        With wsInt
            c = Array(.Range("B11"))
        End With
    
        For i = LBound(c) To UBound(c)
            p(i + 1).Value = c(i).Value
        Next
    
        With wsNDA
            Dim Lastrow As Long
    
            Lastrow = .Range("B" & Rows.Count).End(xlUp).Row + 1
    
            wsInt.Rows("14").Copy
    
            With .Rows(Lastrow)
                .PasteSpecial Paste:=xlPasteFormats
                .PasteSpecial Paste:=xlPasteValues
                .Interior.Pattern = xlNone
            End With
    
            With .Range("A" & Lastrow)
                If Lastrow = 3 Then
                    .Value = 1
                Else
                    .Value = Val(wsNDA.Range("A" & Lastrow - 1).Value) + 1
                End If
    
                .NumberFormat = "0#######"
            End With
        End With
    End Sub
    Now this code submits the data to Form to push to Data to the last row, and gives it a number in column A.

    What I need, is the ability to take the code, make it search for the Number on cell AG3 and once found paste the data into said row, and replace the data there with the updated data.

    I know I can regurgitate this code and change it to look like this.

    Code:
    Option Explicit
    
    Sub ReplaceValues()
        Dim i As Integer
    
        'Worksheets
        Dim wsInt As Worksheet
        Dim wsNDA As Worksheet
    
        'Copy Ranges
        Dim c As Variant
    
        'Paste Ranges
        Dim p As Range
    
        'Setting Sheets
        Set wsInt = Sheets("Internal NCMR")
        Set wsNDA = Sheets("NCMR Data")
        Set p = wsInt.Range("B54:U54")
    
        'Copying cells from first sheet
        With wsInt
            c = Array(.Range("AG3"), .Range("B11"))
        End With
    
        For i = LBound(c) To UBound(c)
            p(i + 1).Value = c(i).Value
        Next
    Now I know:

    Code:
            wsInt.Rows("14").Copy
    Goes next, and this possibly could go next.

    Code:
            With .Rows()
                .PasteSpecial Paste:=xlPasteFormats
                .PasteSpecial Paste:=xlPasteValues
                .Interior.Pattern = xlNone
            End With
    The problem I have is that I don't know how to make it only paste where the original data is.

    Can someone help me here?

    I've looked online and every place I've seen has a pop up that allows automatic replacement of a cell, but not everything. I've also seen it where they just say if x replace with y.

    I don't know if I am looking for the same thing or not, but I can't make heads or tails of most code out there, it seems too "specific" for the purpose of the OP...

    Thanks for any help anyone can supply.

    Here is the sheets.

    Also, if someone need a bare bones answer of what I am looking for it's this:

    The form has a drop down menu with numbers in it, which if you select names appear in the name field. What I want to do is to change the name, update the row, because this will be used for multiple fields ultimately, so that the new name corresponds with the old number instead of having it be at the end of the data.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2012
    Posts
    10
    I'm still not exactly sure of the details, but take a look at this.
    Code:
    Option Explicit
    
    Sub CopyValues()
        Dim i As Integer
        Dim MatchRow As Variant
    
        'Internal NCMR
        Dim wsInt As Worksheet
        Dim wsNDA As Worksheet
    
        'Copy Ranges
        Dim c As Variant
    
        'Paste Ranges
        Dim p As Range
    
        'Setting Sheet
        Set wsInt = Sheets("Form")
        Set wsNDA = Sheets("Data")
        Set p = wsInt.Range("B54:U54")
    
        With wsInt
            c = Array(.Range("AG3"), .Range("B11"))
        End With
    
        For i = LBound(c) To UBound(c)
            p(i + 1).Value = c(i).Value
        Next
    
        With wsNDA
            Dim Lastrow As Long
    
            Lastrow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
            
            MatchRow = Application.WorksheetFunction.Match(wsInt.Range("AG3"), _
                .Range("A3:A" & Lastrow)) + 2
            
            If MatchRow <> 0 Then Lastrow = MatchRow
            
            wsInt.Range("A14:F14").Copy
    
            With .Range("C" & Lastrow)
                .PasteSpecial Paste:=xlPasteFormats
                .PasteSpecial Paste:=xlPasteValues
                .Interior.Pattern = xlNone
            End With
    
            With .Range("A" & Lastrow)
                If Lastrow = 3 Then
                    .Value = 1
                Else
                    .Value = Val(wsNDA.Range("A" & Lastrow - 1).Value) + 1
                End If
    
                .NumberFormat = "0#######"
            End With
        End With
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •