Locate Cell value and set Cell to active - 2007 user

MonsterBait

New Member
Joined
May 17, 2012
Messages
10
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:
Title
Record Number
Name
Description
Date
1
Tom
Test Alpha
1/04/2011
2
****
Test Bravo
4/06/2011
6
Harry
Test Charlie
7/08/2011
7
Vicki
Test Delta
16/08/2011
8
Heather
Test Echo
2/01/2012

<TBODY>
</TBODY>





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

Thanks,
Alex
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

fredlo2008

Active Member
Joined
Jan 12, 2012
Messages
254
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

New Member
Joined
May 17, 2012
Messages
10
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

Active Member
Joined
Jan 12, 2012
Messages
254
To use code tags go to advanced and select the code, then press the button with the # symbol

Code:
Your code here
 

MonsterBait

New Member
Joined
May 17, 2012
Messages
10
Thanks fredlo,

I learnt something new.

Code:
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

Active Member
Joined
Jan 12, 2012
Messages
254
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

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
Top