Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
formula : =(column A)+(column B)-(column C). Why won't it work? | Excel Discussion (Misc queries) |