macro column lookup enter data in another column same row
Mike,
The looping code works fine; it's you MakeNewPassword routine that is wrong.
I see no connection between the CompanyName and actually making the
password. Surely you need to set some cell value = CompanyName to actually
change something.
You only need to return the password from the function, not reset this
hardcoded cell each. So delete this line:
Sheets("Sheet1").Range("F13") = "PASS" & MakeNewPassword
NickHK
"mikeolson" wrote in message
...
It works for a single password only. It found my client in A13, retrieved
the password from my 2nd sheet based on the name of the client scrambled
and
placed the unique password in F13. It did not go to A14 and get their
password and post it in F14. Sheet 2 cell A1 & A2 scrambles & "codes"
their
company name and A3 puts them together to create the password. Here is
what
I used:
'
Sub Button1_Click()
Dim Cell As Range
For Each Cell In Range(Range("a13"), Range("a13").End(xlDown))
Cell.Offset(0, 6).Value = MakeNewPassword(Cell.Value)
Next
End Sub
Private Function MakeNewPassword(CompanyName As String) As String
Dim pass1 As String
Dim pass2 As String
pass1 = Sheets("Sheet2").Range("A1")
pass2 = Sheets("Sheet2").Range("A2")
MakeNewPassword = Sheets("Sheet2").Range(pass1) &
Sheets("Sheet2").Range(pass2)
Sheets("Sheet2").Range("A3") = "PASS" & MakeNewPassword
Sheets("Sheet1").Range("F13") = "PASS" & MakeNewPassword
End Function
'
So when it's all done it would assign as many unique passwords in column F
starting at row 13 as there are unique company names in column A starting
at
row 13 until it finds an empty cell in column A, it stops and does not
place
a password in column F corresponding to the blank cell in A
Thank you for your help on this!
Mike
"NickHK" wrote:
Mike,
Something like this ?
Private Sub CommandButton2_Click()
Dim Cell As Range
For Each Cell In Range(Range("a13"), Range("a13").End(xlDown))
Cell.Offset(0, 6).Value = MakeNewPassword(Cell.Value)
Next
End Sub
Private Function MakeNewPassword(CompanyName As String) As String
'However you generate the PW
MakeNewPassword = StrReverse(CompanyName)
End Function
NickHK
"mikeolson" wrote in message
...
I have a company client list in column A rows 13:62. Column F is where
I
put
their new password, Column H is the contact name, column I is their
e-mail
address. I have a macro that looks at the name in A13, calculates a
unique
password and places it in F13. I want the macro to move on to A14 and
put
their password in F14 and so on until it sees an empty cell in column
A.
|