Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
That didn't work either.
1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
Mike,
From your description, I still have the feeling that you are only generating a password for the first company. e.g. Sheet2!A1=Pass1(Sheet1!A13) "B10" Sheet2!A2=Pass2(Sheet1!A13) "D20" If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot process any other companies located in Sheet1 A14+. You need to update Sheet2 to point to A14 onwards, otherwise there is no connection between your company list and generating a Password for each. However, as you have a worksheet method of password generation, why not just fill down a column on Sheet1 with these formulas instead and forget the VBA. Seems that worksheet function INDIRECT may be useful to you here. Unless I'm missing something, which is quite likely... NickHK "mikeolson" wrote in message ... That didn't work either. 1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
You're partially right, it is only looking at A13, so here's what I did
In Sheet2 cell A4 I have the macro copy over cell A13 on down from Sheet 1. This is hard to explain. I cannot fill down on Sheet 1 as the formula is extremely complex it looks at a grid for PASS1 10 columns by 96 rows and for PASS2 26 columns by 96 rows. So, here's where I'm at now. Sheet2 cell A4 is currently set to =Sheet1!A13 A1 formula randomizes charachters and generates PASS1, we'll use B10 (contains 1234) for company 1 A2 formula randomizes charachters and generates PASS2, we'll use D20 (contains 4567) for company 1 Macro concatenates PASS + CELL B10 + CELL D20 = PASS12345678 I need this value placed in F13 Then my macro goes to Sheet 1 A14 and places that value in Sheet2 A4 which in turn changes the value in A1 & A2, but A3 is generated from the MakeNewPassword macro, which I cannot get it to do, then place that value in F14. I am getting it to change A4 from every company starting at A13 until it finds an empty cell. Here's what I have now: ' Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Sheets("Sheet2").Range("B4") = Cell.Offset(0, 0) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String pass1 = Sheets("Sheet2").Range("B1") pass2 = Sheets("Sheet2").Range("B2") MakeNewPassword = Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("B3") = "PASS" & MakeNewPassword Sheets("Sheet1").Range("f13") = "PASS" & MakeNewPassword End Function ' With this it places the password in F13. In Sheet 2 cell A4 I have the last company name on my list so I know it's going down the list which get's the PASS1 & PASS2, I just need the macro to get those values from the 2 grids and place them in A3 and in F14 on down. Thanks again Mike "NickHK" wrote: Mike, From your description, I still have the feeling that you are only generating a password for the first company. e.g. Sheet2!A1=Pass1(Sheet1!A13) "B10" Sheet2!A2=Pass2(Sheet1!A13) "D20" If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot process any other companies located in Sheet1 A14+. You need to update Sheet2 to point to A14 onwards, otherwise there is no connection between your company list and generating a Password for each. However, as you have a worksheet method of password generation, why not just fill down a column on Sheet1 with these formulas instead and forget the VBA. Seems that worksheet function INDIRECT may be useful to you here. Unless I'm missing something, which is quite likely... NickHK "mikeolson" wrote in message ... That didn't work either. 1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
I just ran it again and the PASSWORD IN F13 is not the correct password for
A13, it is for the last company listed in column A "NickHK" wrote: Mike, From your description, I still have the feeling that you are only generating a password for the first company. e.g. Sheet2!A1=Pass1(Sheet1!A13) "B10" Sheet2!A2=Pass2(Sheet1!A13) "D20" If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot process any other companies located in Sheet1 A14+. You need to update Sheet2 to point to A14 onwards, otherwise there is no connection between your company list and generating a Password for each. However, as you have a worksheet method of password generation, why not just fill down a column on Sheet1 with these formulas instead and forget the VBA. Seems that worksheet function INDIRECT may be useful to you here. Unless I'm missing something, which is quite likely... NickHK "mikeolson" wrote in message ... That didn't work either. 1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
Mike,
OK. You have a mix of worksheet functions and VBA that could be written more cleanly in one or the other, but But try this: Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String 'You said you were using A4 to hold the ComapnyName With Sheets("Sheet2") .Range("A4").Value = CompanyName pass1 = .Range("B1") pass2 = .Range("B2") MakeNewPassword = "PASS" & .Range(pass1) & .Range(pass2) End With End Function NickHK "mikeolson" wrote in message ... You're partially right, it is only looking at A13, so here's what I did In Sheet2 cell A4 I have the macro copy over cell A13 on down from Sheet 1. This is hard to explain. I cannot fill down on Sheet 1 as the formula is extremely complex it looks at a grid for PASS1 10 columns by 96 rows and for PASS2 26 columns by 96 rows. So, here's where I'm at now. Sheet2 cell A4 is currently set to =Sheet1!A13 A1 formula randomizes charachters and generates PASS1, we'll use B10 (contains 1234) for company 1 A2 formula randomizes charachters and generates PASS2, we'll use D20 (contains 4567) for company 1 Macro concatenates PASS + CELL B10 + CELL D20 = PASS12345678 I need this value placed in F13 Then my macro goes to Sheet 1 A14 and places that value in Sheet2 A4 which in turn changes the value in A1 & A2, but A3 is generated from the MakeNewPassword macro, which I cannot get it to do, then place that value in F14. I am getting it to change A4 from every company starting at A13 until it finds an empty cell. Here's what I have now: ' Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Sheets("Sheet2").Range("B4") = Cell.Offset(0, 0) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String pass1 = Sheets("Sheet2").Range("B1") pass2 = Sheets("Sheet2").Range("B2") MakeNewPassword = Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("B3") = "PASS" & MakeNewPassword Sheets("Sheet1").Range("f13") = "PASS" & MakeNewPassword End Function ' With this it places the password in F13. In Sheet 2 cell A4 I have the last company name on my list so I know it's going down the list which get's the PASS1 & PASS2, I just need the macro to get those values from the 2 grids and place them in A3 and in F14 on down. Thanks again Mike "NickHK" wrote: Mike, From your description, I still have the feeling that you are only generating a password for the first company. e.g. Sheet2!A1=Pass1(Sheet1!A13) "B10" Sheet2!A2=Pass2(Sheet1!A13) "D20" If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot process any other companies located in Sheet1 A14+. You need to update Sheet2 to point to A14 onwards, otherwise there is no connection between your company list and generating a Password for each. However, as you have a worksheet method of password generation, why not just fill down a column on Sheet1 with these formulas instead and forget the VBA. Seems that worksheet function INDIRECT may be useful to you here. Unless I'm missing something, which is quite likely... NickHK "mikeolson" wrote in message ... That didn't work either. 1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
Just got it
' Sub Button1_Click() Dim Cell As Range 'makes it start at A13, I was getting F13 the same value as F14 password Sheets("Sheet2").Range("A4") = Sheets("Sheet1").Range("A13") For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Sheets("Sheet2").Range("B99") = Cell.Offset(0, 0) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String pass1 = Sheets("Sheet2").Range("B104") pass2 = Sheets("Sheet2").Range("B105") MakeNewPassword = "PASS" & Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("B106") = MakeNewPassword End Function ' I have tried it several times and I have the results I am looking for. Thank you very much for your help! Mike "NickHK" wrote: Mike, OK. You have a mix of worksheet functions and VBA that could be written more cleanly in one or the other, but But try this: Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String 'You said you were using A4 to hold the ComapnyName With Sheets("Sheet2") .Range("A4").Value = CompanyName pass1 = .Range("B1") pass2 = .Range("B2") MakeNewPassword = "PASS" & .Range(pass1) & .Range(pass2) End With End Function NickHK "mikeolson" wrote in message ... You're partially right, it is only looking at A13, so here's what I did In Sheet2 cell A4 I have the macro copy over cell A13 on down from Sheet 1. This is hard to explain. I cannot fill down on Sheet 1 as the formula is extremely complex it looks at a grid for PASS1 10 columns by 96 rows and for PASS2 26 columns by 96 rows. So, here's where I'm at now. Sheet2 cell A4 is currently set to =Sheet1!A13 A1 formula randomizes charachters and generates PASS1, we'll use B10 (contains 1234) for company 1 A2 formula randomizes charachters and generates PASS2, we'll use D20 (contains 4567) for company 1 Macro concatenates PASS + CELL B10 + CELL D20 = PASS12345678 I need this value placed in F13 Then my macro goes to Sheet 1 A14 and places that value in Sheet2 A4 which in turn changes the value in A1 & A2, but A3 is generated from the MakeNewPassword macro, which I cannot get it to do, then place that value in F14. I am getting it to change A4 from every company starting at A13 until it finds an empty cell. Here's what I have now: ' Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Sheets("Sheet2").Range("B4") = Cell.Offset(0, 0) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String pass1 = Sheets("Sheet2").Range("B1") pass2 = Sheets("Sheet2").Range("B2") MakeNewPassword = Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("B3") = "PASS" & MakeNewPassword Sheets("Sheet1").Range("f13") = "PASS" & MakeNewPassword End Function ' With this it places the password in F13. In Sheet 2 cell A4 I have the last company name on my list so I know it's going down the list which get's the PASS1 & PASS2, I just need the macro to get those values from the 2 grids and place them in A3 and in F14 on down. Thanks again Mike "NickHK" wrote: Mike, From your description, I still have the feeling that you are only generating a password for the first company. e.g. Sheet2!A1=Pass1(Sheet1!A13) "B10" Sheet2!A2=Pass2(Sheet1!A13) "D20" If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot process any other companies located in Sheet1 A14+. You need to update Sheet2 to point to A14 onwards, otherwise there is no connection between your company list and generating a Password for each. However, as you have a worksheet method of password generation, why not just fill down a column on Sheet1 with these formulas instead and forget the VBA. Seems that worksheet function INDIRECT may be useful to you here. Unless I'm missing something, which is quite likely... NickHK "mikeolson" wrote in message ... That didn't work either. 1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
I thought I had it. This code puts the password in the wrong place though.
company (A) password (F) A13 A18 (or whatever the last company listed is) A14 A13 A15 A14 A16 A15 A17 A16 A18 A17 ' Sub Button1_Click() Dim Cell As Range Sheets("Sheet2").Range("A4") = Sheets("Sheet1").Range("c13") For Each Cell In Range(Range("c13"), Range("c13").End(xlDown)) Cell.Offset(0, 2).Value = MakeNewPassword(Cell.Value) Sheets("Sheet2").Range("A4") = Cell.Offset(0, 0) 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 = "PASS" & Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("A3") = MakeNewPassword End Function ' I'm so close now. Where did I go wrong? Thanks! Mike "NickHK" wrote: Mike, OK. You have a mix of worksheet functions and VBA that could be written more cleanly in one or the other, but But try this: Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String 'You said you were using A4 to hold the ComapnyName With Sheets("Sheet2") .Range("A4").Value = CompanyName pass1 = .Range("B1") pass2 = .Range("B2") MakeNewPassword = "PASS" & .Range(pass1) & .Range(pass2) End With End Function NickHK "mikeolson" wrote in message ... You're partially right, it is only looking at A13, so here's what I did In Sheet2 cell A4 I have the macro copy over cell A13 on down from Sheet 1. This is hard to explain. I cannot fill down on Sheet 1 as the formula is extremely complex it looks at a grid for PASS1 10 columns by 96 rows and for PASS2 26 columns by 96 rows. So, here's where I'm at now. Sheet2 cell A4 is currently set to =Sheet1!A13 A1 formula randomizes charachters and generates PASS1, we'll use B10 (contains 1234) for company 1 A2 formula randomizes charachters and generates PASS2, we'll use D20 (contains 4567) for company 1 Macro concatenates PASS + CELL B10 + CELL D20 = PASS12345678 I need this value placed in F13 Then my macro goes to Sheet 1 A14 and places that value in Sheet2 A4 which in turn changes the value in A1 & A2, but A3 is generated from the MakeNewPassword macro, which I cannot get it to do, then place that value in F14. I am getting it to change A4 from every company starting at A13 until it finds an empty cell. Here's what I have now: ' Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Sheets("Sheet2").Range("B4") = Cell.Offset(0, 0) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String pass1 = Sheets("Sheet2").Range("B1") pass2 = Sheets("Sheet2").Range("B2") MakeNewPassword = Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("B3") = "PASS" & MakeNewPassword Sheets("Sheet1").Range("f13") = "PASS" & MakeNewPassword End Function ' With this it places the password in F13. In Sheet 2 cell A4 I have the last company name on my list so I know it's going down the list which get's the PASS1 & PASS2, I just need the macro to get those values from the 2 grids and place them in A3 and in F14 on down. Thanks again Mike "NickHK" wrote: Mike, From your description, I still have the feeling that you are only generating a password for the first company. e.g. Sheet2!A1=Pass1(Sheet1!A13) "B10" Sheet2!A2=Pass2(Sheet1!A13) "D20" If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot process any other companies located in Sheet1 A14+. You need to update Sheet2 to point to A14 onwards, otherwise there is no connection between your company list and generating a Password for each. However, as you have a worksheet method of password generation, why not just fill down a column on Sheet1 with these formulas instead and forget the VBA. Seems that worksheet function INDIRECT may be useful to you here. Unless I'm missing something, which is quite likely... NickHK "mikeolson" wrote in message ... That didn't work either. 1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
I got it now, I had to switch a couple lines around. I was getting the
password for client 2 before having the formula look to client 2. Here is what I used: ' Sub Button1_Click() Dim Cell As Range Sheets("Sheet2").Range("A4") = Sheets("Sheet1").Range("C13") For Each Cell In Range(Range("C13"), Range("C13").End(xlDown)) Sheets("Sheet2").Range("A4") = Cell.Offset(0, 0) Cell.Offset(0, 2).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 = "PASS" & Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("A3") = MakeNewPassword End Function ' Where Column C in Sheet one contains the client list starting at row 13 Column E contains the placement of their password Thanks again for all your help! Mike "mikeolson" wrote: I thought I had it. This code puts the password in the wrong place though. company (A) password (F) A13 A18 (or whatever the last company listed is) A14 A13 A15 A14 A16 A15 A17 A16 A18 A17 ' Sub Button1_Click() Dim Cell As Range Sheets("Sheet2").Range("A4") = Sheets("Sheet1").Range("c13") For Each Cell In Range(Range("c13"), Range("c13").End(xlDown)) Cell.Offset(0, 2).Value = MakeNewPassword(Cell.Value) Sheets("Sheet2").Range("A4") = Cell.Offset(0, 0) 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 = "PASS" & Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("A3") = MakeNewPassword End Function ' I'm so close now. Where did I go wrong? Thanks! Mike "NickHK" wrote: Mike, OK. You have a mix of worksheet functions and VBA that could be written more cleanly in one or the other, but But try this: Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String 'You said you were using A4 to hold the ComapnyName With Sheets("Sheet2") .Range("A4").Value = CompanyName pass1 = .Range("B1") pass2 = .Range("B2") MakeNewPassword = "PASS" & .Range(pass1) & .Range(pass2) End With End Function NickHK "mikeolson" wrote in message ... You're partially right, it is only looking at A13, so here's what I did In Sheet2 cell A4 I have the macro copy over cell A13 on down from Sheet 1. This is hard to explain. I cannot fill down on Sheet 1 as the formula is extremely complex it looks at a grid for PASS1 10 columns by 96 rows and for PASS2 26 columns by 96 rows. So, here's where I'm at now. Sheet2 cell A4 is currently set to =Sheet1!A13 A1 formula randomizes charachters and generates PASS1, we'll use B10 (contains 1234) for company 1 A2 formula randomizes charachters and generates PASS2, we'll use D20 (contains 4567) for company 1 Macro concatenates PASS + CELL B10 + CELL D20 = PASS12345678 I need this value placed in F13 Then my macro goes to Sheet 1 A14 and places that value in Sheet2 A4 which in turn changes the value in A1 & A2, but A3 is generated from the MakeNewPassword macro, which I cannot get it to do, then place that value in F14. I am getting it to change A4 from every company starting at A13 until it finds an empty cell. Here's what I have now: ' Sub Button1_Click() Dim Cell As Range For Each Cell In Range(Range("a13"), Range("a13").End(xlDown)) Cell.Offset(0, 5).Value = MakeNewPassword(Cell.Value) Sheets("Sheet2").Range("B4") = Cell.Offset(0, 0) Next End Sub Private Function MakeNewPassword(CompanyName As String) As String Dim pass1 As String Dim pass2 As String pass1 = Sheets("Sheet2").Range("B1") pass2 = Sheets("Sheet2").Range("B2") MakeNewPassword = Sheets("Sheet2").Range(pass1) & Sheets("Sheet2").Range(pass2) Sheets("Sheet2").Range("B3") = "PASS" & MakeNewPassword Sheets("Sheet1").Range("f13") = "PASS" & MakeNewPassword End Function ' With this it places the password in F13. In Sheet 2 cell A4 I have the last company name on my list so I know it's going down the list which get's the PASS1 & PASS2, I just need the macro to get those values from the 2 grids and place them in A3 and in F14 on down. Thanks again Mike "NickHK" wrote: Mike, From your description, I still have the feeling that you are only generating a password for the first company. e.g. Sheet2!A1=Pass1(Sheet1!A13) "B10" Sheet2!A2=Pass2(Sheet1!A13) "D20" If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot process any other companies located in Sheet1 A14+. You need to update Sheet2 to point to A14 onwards, otherwise there is no connection between your company list and generating a Password for each. However, as you have a worksheet method of password generation, why not just fill down a column on Sheet1 with these formulas instead and forget the VBA. Seems that worksheet function INDIRECT may be useful to you here. Unless I'm missing something, which is quite likely... NickHK "mikeolson" wrote in message ... That didn't work either. 1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro column lookup enter data in another column same row
I too was looking for a way to create passwords and came across this post. I
am not trying to look across different worksheets, which makes it easier. I have the person's last name in Column C and the Passwords will go in Column D. I have a header row, so my data starts on Row 2. It works nicely except I have two questions, 1) The current code takes the last value from Column C and places it in Column D, Row 2. This is not correct. It should take the value from Column C, Row 2 and concatenate it as it does with the rest of the cells. How do I make it do that? 2) Is there a way to scramble "C2"? For example, if a person's last name is "Smith", it generates the following password: "&pSmith!V". I would rather it scramble the "Smith" part of the password. So it might read for example, "&phSimt!V". Is this possible? Here's the code I modified from the earlier post: ---------------------------------------------------------------- Sub Password() Dim Cell As Range For Each Cell In Range(Range("c2"), Range("c2").End(xlDown)) Cell.Offset(0, 1).Value = MakeNewPassword(Cell.Value) Next End Sub Private Function MakeNewPassword(UserName As String) As String Dim pass1 As String 'Using C1 to hold the UserName With Sheets("Phase_1") .Range("d2").Value = UserName pass1 = .Range("d2") MakeNewPassword = "&p" & pass1 & "!V" End With End Function ------------------------------------------------------------ Thanks for any help! "mikeolson" wrote: I just ran it again and the PASSWORD IN F13 is not the correct password for A13, it is for the last company listed in column A "NickHK" wrote: Mike, From your description, I still have the feeling that you are only generating a password for the first company. e.g. Sheet2!A1=Pass1(Sheet1!A13) "B10" Sheet2!A2=Pass2(Sheet1!A13) "D20" If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot process any other companies located in Sheet1 A14+. You need to update Sheet2 to point to A14 onwards, otherwise there is no connection between your company list and generating a Password for each. However, as you have a worksheet method of password generation, why not just fill down a column on Sheet1 with these formulas instead and forget the VBA. Seems that worksheet function INDIRECT may be useful to you here. Unless I'm missing something, which is quite likely... NickHK "mikeolson" wrote in message ... That didn't work either. 1) Sheet 1, A13 has: "company" 2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in that cell is a value (I have a large grid with random numbers in it) 3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in that cell is a value 4) My macro looks up the value in cell B10 & it's 1234 and looks up the value in cell D20 and it's 5678, concatenates PASS+1234+5678 = PASS12345678 and places it in Sheet 2, A3 and Sheet 1, F13 5) Since each client will get a new code in Sheet 2 A1 & A2 to get from the grid, A3 will have a new PASS******** 6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3 are unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as it looks for the 2nd customer name, it needs to place their password in A3 and F14, the 3rd customer would need to be in A3 (as they all will) and F15 and so on This is where I am stuck. It looks up Sheet 1 A13, calculates the values in Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1 & A2 and gives me the final password in A3 and I have it placed again in Sheet 1 on the same row as that customer (F13), I cannot get it to repeat this process on A14&F14 until it sees a blank line in column A, which means there's no customer there, I then do not need a password. I also have a column N with a yes or no in it for e-mailing the password, this works, if the yes or no should be the verification to create a password or not that would work also. Thank you for your time, this one's driving me crazy! :) Mike "NickHK" wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
enter data in the same column in multiple sheets | New Users to Excel | |||
Macro to select cells in column enter data then press enter | New Users to Excel | |||
i don't want to enter dublication data at a column | New Users to Excel | |||
Macro to enter record number in column A | Excel Programming | |||
How to enter data in row in popoulated column | Excel Programming |