Ack. Going to another column Take 3. Sorry!
 

Hey, sorry about that, I know it must be frustrating you to see tw
empty posts by me, but it was a problem with excelforum I guess. Jus
know that i lost my entire post and now have to rewrite this pos
again. lol.

So I posted a few days back a problem I had in the miscellaneous foru
and was sort of referred to here since the solution involved a bit o
macro and VBA work. Here it goes, I hope someone can help cuz I hav
little to no experience in that language (I do have C++ experience, s
i'm not totally brainless though).

So I have two worksheets. On the first worksheet, which will be eithe
hidden or protected, I have a list of Site Numbers. Site Numbers ar
merely 1 to 4 digit numbers. Each site number is given 1-4 passwords
depending on the amount of digits in the site number. For example, sit
321 will have 3 passwords and site 8 will have only one. Thos
passwords are listed in the next 4 columns. For site 8, it will onl
have one password in the second column, and then the next three wil
just be blank. That's the convention.

Anyways, in the next free column (the sixth column) lists the Curren
Password. The current password a password from the list of 4 passwords
To start, the current password is the first password listed (ie. th
second column). There is also another column called Next Password
which lists each sites next password after their current one. For on
digit site numbers, the next password will be the same password as th
current, since they only have one password. If a current password is a
the end of the list of passwords, the Next Password will be the firs
password again. So it circles around.

Basically, I have to make a small thing where it asks a user to ente
their site number (prolly through an input box would be easiest), an
on a separate worksheet, it lists JUST that site number's Curren
Password and then it's New Password (ie, the one on the Next Passwor
column). btw, the site number has to be in the database or else it wil
tell an error. My problem is updating the database once a user asks fo
a new password. Because after that new password is given, the Nex
Password now becomes the Current Password and the next password in th
list must now become the new Next Password. I'm sure this sounds reall
confusing, but yet you know what I'm talking about. Here's a diagram:

A--------B--------C--------D------E---------F-----------G
SiteNo--PW1-----PW2-----PW3---PW4-----Curr.--------Next
1-------d1-----------------------------------d1------------d1
6874---h4---------h43-----h9---h23------h4-------------h43

and so on...

So if someone asks for a new password for Site 6874, it would tell th
user the current password (h4) and then the new one (h43). But it als
has to update the database so that JUST 6874's new current password i
h43 and its new Next password is h9.

Think you can help? Any help would be greatly appreciated, especiall
in terms of some code or macros to help me get started. Thank you s
much in advance

--
Message posted from http://www.ExcelForum.com

Ack. Going to another column Take 3. Sorry!
 

Nornny

Try this on a back up copy of your data

Change "sheet1" & "sheet2" as required
tested with sheet2 hidden

Sub dddd()
Dim wsPass As Worksheet
Dim wsDisp As Worksheet

Dim sSite As String
Dim lRow As Long

Set wsPass = Sheets("sheet2")
Set wsDisp = Sheets("sheet1")

sSite$ = InputBox("Please Enter Site ID", "Site ID")
Select Case sSite
Case "", vbCancel
End
End Select
On Error Resume Next
lRow = wsPass.Range("a:a").Find(What:=sSite, _
After:=Range("a1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row
On Error GoTo 0
If lRow = 0 Then
MsgBox "Site ID Not Found"
End
End If
wsDisp.Range("a2").Value = sSite
wsDisp.Range("b2").Value = wsPass.Cells(lRow, "f").Value
wsDisp.Range("c2").Value = wsPass.Cells(lRow, "g").Value

wsPass.Cells(lRow, "f").Cut
wsPass.Cells(lRow, "f").Offset(0, Len(sSite)).Insert Shift:=xlToRight

End Su

--
Message posted from http://www.ExcelForum.com

Ack. Going to another column Take 3. Sorry!
 

Hey! Thanks so much for the help. It's not quite working right, but it'
definately more than what I had going. lol. The problem is, I enter th
site number (say site 5432), and it finds it and gives me the curren
pw and then the next pw on sheet 1 like it's supposed to. In tha
proccess, it moves the new password to the current password column. Bu
the old password gets moved across two spaces to Column I and the Nex
Password column now remains blank, when it should show the nex
password in the list.

If you know what I'm talking about, I'd love to hear some advice or ge
some more coding snippets! This is a great start from which I think
can work with! Thank you so much

--
Message posted from http://www.ExcelForum.com