Locate Cell value and set Cell to active - 2007 user

[MonsterBait]

Hi All,

I wrote a bit of code that's been working great until now. The Records have reached over 1100 entries and my code doesn't do what I designed it for anymore.

I just need the segment of code to locate a record number in column "A" and make that the active cell.

The offending code (which I wrote and had been working probably for the wrong reasons) is:

Dim CurCell as Range

num2 = Range("UpdateNumber")
Set CurCell = WorkBooks("Database.xlsx").Sheets("Records").Range("$A$2:$A$2000").Cells(num2)
[additional code here which updates the record]

**UpdateNumber is a named range which holds a record number the user has input.
**Some records have been deleted and all blank/empty rows have been removed.
**The first 2 rows of the database are title/headings

Scaled down sample database:
[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Title
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Record Number
[/TD]
[TD]Name
[/TD]
[TD]Description
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Tom
[/TD]
[TD]Test Alpha
[/TD]
[TD]1/04/2011
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]****
[/TD]
[TD]Test Bravo
[/TD]
[TD]4/06/2011
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Harry
[/TD]
[TD]Test Charlie
[/TD]
[TD]7/08/2011
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Vicki
[/TD]
[TD]Test Delta
[/TD]
[TD]16/08/2011
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Heather
[/TD]
[TD]Test Echo
[/TD]
[TD]2/01/2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]





I would be very grateful if someone could point out what I'm doing wrong and hopefully suggest a solution.

Thanks,
Alex

 

[fredlo2008]

Hi Alex,

Can you be a bit more descriptive about your problem, for ex: do you get an error, it messes up the sheet? It would be good if you upload a sample of the workbook and the whole code so we can actually see where the issue is.

Also use code tags around your code. It will be easier to read and copy.

Thanks

 

[MonsterBait]

Hi fredlo,

I'm still new to this so you'll have to explain what is "code tag"?
There is no error message or anything that tells me there is a problem.
However, instead of updating record number 1129 (which is phyiscally located on row 786), it would skip down to row 1131 and paste the updated record there.

I've paste the code here but have scaled back the number of columns.

Sub UpdateExistingEntry()
Dim CurCell As Range
Dim UpdateData() As String
Dim num1 As Long, num2 As Long
Dim DestinWorkBook As Workbook
Dim ExcelApp As New Excel.Application

' Warning message

Dim Ans As Integer

Ans = MsgBox("Are you sure you wish to edit this entry?", vbYesNo + vbExclamation)
If Ans = vbNo Then Exit Sub

' Open source workbook

Set DestinWorkBook = ExcelApp.Workbooks.Open(DestinationPath)

' Set active cell to corresponding record number in database

num2 = Range("UpdateNumber")
Set CurCell = DestinWorkBook.Sheets("Records").Range("$A$2:$A$4000").Cells(num2)


ReDim UpdateData(1 To 3) As String
UpdateData(1) = "UpdateName"
UpdateData(2) = "UpdateDescription"
UpdateData(3) = "UpdateDate"
For num1 = 1 To UBound(UpdateData)
CurCell.Offset(0, num1).Value = Range(UpdateData(num1)).Value
Range(UpdateData(num1)).Value = ""
Next num1

' Save and close destination file

With DestinWorkBook
.Save
.Close
End With
Set DestinWorkBook = Nothing

ExcelApp.Quit
Set ExcelApp = Nothing

Application.ScreenUpdating = True

End Sub


Thanks,
Alex

 

[fredlo2008]

To use code tags go to advanced and select the code, then press the button with the # symbol

Your code here

 

[MonsterBait]

Thanks fredlo,

I learnt something new.

Sub UpdateExistingEntry()

Dim CurCell As Range
Dim UpdateData() As String
Dim num1 As Long, num2 As Long
Dim DestinWorkBook As Workbook
Dim ExcelApp As New Excel.Application

' Warning message

   Dim Ans As Integer

   Ans = MsgBox("Are you sure you wish to edit this entry?", vbYesNo + vbExclamation)
   If Ans = vbNo Then Exit Sub

' Open source workbook

   Set DestinWorkBook = ExcelApp.Workbooks.Open(DestinationPath)

' Set active cell to corresponding record number in database

   num2 = Range("UpdateNumber")
   Set CurCell = DestinWorkBook.Sheets("Records").Range("$A$2:$A$4000").Cells(num2)


   ReDim UpdateData(1 To 3) As String
   UpdateData(1) = "UpdateName"
   UpdateData(2) = "UpdateDescription"
   UpdateData(3) = "UpdateDate"

For num1 = 1 To UBound(UpdateData)
   CurCell.Offset(0, num1).Value = Range(UpdateData(num1)).Value
   Range(UpdateData(num1)).Value = ""
Next num1

' Save and close destination file

With DestinWorkBook
.Save
.Close
End With
Set DestinWorkBook = Nothing

ExcelApp.Quit
Set ExcelApp = Nothing

Application.ScreenUpdating = True

End Sub

 

[fredlo2008]

Hi Monster,

I am getting an error when running your code on variable DestinationPath. I ma wondering if you are getting the same. maybe there is a peice of code that declares that you left out of your copy. Something else is the defined names like

"UpdateNumber" how are they defined?

Thanks​