0

I am new to VBA but I am working on setting up a Database in Excel (I realize that Access is much better suitedm but I am doing this for someone else).

I have a source file that has information in the range B5:B17, this form will be for others to send to the person managing the database. I need to write a VBA code that will select the data in the source range, transpose it, find a match is one exists, then either overwrite the existing data or add to the next blank row. Here is the sequence:

  1. Prompts the database manager to open the source file (I know how to do this)
  2. Transpose the data in B5:B17
  3. Search for a match in cell B7 (source file) and match it to values in column C (database)
  4. Overwrite the matching data
  5. If no match exists, then write to the next empty row.

I have been using the following code as a guide but it has some limitations. The source file has to be open, also, I am not sure how to include the Transpose function to this code. Any help id appreciated

Community
  • 1
  • 1
user3390454
  • 1

1 Answers1

0

This should do what you are looking for.

Sub Sample()
Dim rngEnteredID As Range
Dim lngRowWithMatch As Range

Set rngEnteredID = Sheets("Sheet1").Range("B7")

On Error GoTo NoMatch
lngRowWithMatch = WorksheetFunction.Match(rngEnteredID.Value, Sheets("Sheet2").Range("C:C"), 0)
On Error GoTo 0
Sheets("Sheet2").Range("A" & lngRowWithMatch & ":K" & lngRowWithMatch).Value = Application.Transpose(Sheets("Sheet1").Range("B5:B17"))
Exit Sub

NoMatch:
Dim lngEmptyRow As Long
lngEmptyRow = Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Row + 1

Sheets("Sheet2").Range("A" & lngEmptyRow & ":K" & lngEmptyRow).Value = Application.Transpose(Sheets("Sheet1").Range("B5:B17"))
End Sub
user2140261
  • 7,855
  • 7
  • 32
  • 45