I have created a form in excel and an address book on a separate worksheet.
The form is a basic contract for advertising and I have a field for contract
number and then fields for the name and address of the company that contract
number belongs to. I want to be able to say "if the contract number is "x,"
populate the name and address fields with the corresponding information from
the name and address list on the second worksheet." I have tried all sorts
of tricks, but nothing is working. Any thoughts? Thank you so much.
--
Deborah

Dave Peterson came up with this solution in may 2005 - Maybe it will work for
you:

If you lay out your data in rows (going across), you could use column A as
an indicator with your x. If column A is empty, then skip that row. If it's got something in it, then process it.

Option Explicit
Sub testme()

Dim fWks As Worksheet
Dim tWks As Worksheet

Dim fCol As Variant
Dim tAddr As Variant

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCtr As Long

fCol = Array("b", "c", "e") 'd skipped as an example
tAddr = Array("b12", "c19", "x45")

If UBound(fCol) < UBound(tAddr) Then
MsgBox "design error--not same number of columns/cells)"
End If

Set fWks = Worksheets("Input")
Set tWks = Worksheets("Master")

With fWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
If IsEmpty(.Cells(iRow, "A")) Then
'skip this row
Else
For iCtr = LBound(fCol) To UBound(fCol)
tWks.Range(tAddr(iCtr)).Value _
= .Cells(iRow, fCol(iCtr)).Value
Next iCtr
tWks.PrintOut preview:=True
End If
Next iRow
End With

End Sub

You can modify these two lines:

fCol = Array("b", "c", "e") 'd skipped as an example
tAddr = Array("b12", "c19", "x45")

To map the column to the address.

And I used these two lines to specify the worksheet names.

Set fWks = Worksheets("Input")
Set tWks = Worksheets("Master")

Hope this helps


"Deborah" wrote:

I have created a form in excel and an address book on a separate worksheet.
The form is a basic contract for advertising and I have a field for contract
number and then fields for the name and address of the company that contract
number belongs to. I want to be able to say "if the contract number is "x,"
populate the name and address fields with the corresponding information from
the name and address list on the second worksheet." I have tried all sorts
of tricks, but nothing is working. Any thoughts? Thank you so much.
--
Deborah

And Debra Dalgleish added some stuff he
http://contextures.com/xlForm03.html
and maybe he
http://contextures.com/xlForm02.html


Ron@Buy wrote:

Dave Peterson came up with this solution in may 2005 - Maybe it will work for
you:

If you lay out your data in rows (going across), you could use column A as
an indicator with your x. If column A is empty, then skip that row. If it's got something in it, then process it.

Option Explicit
Sub testme()

Dim fWks As Worksheet
Dim tWks As Worksheet

Dim fCol As Variant
Dim tAddr As Variant

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCtr As Long

fCol = Array("b", "c", "e") 'd skipped as an example
tAddr = Array("b12", "c19", "x45")

If UBound(fCol) < UBound(tAddr) Then
MsgBox "design error--not same number of columns/cells)"
End If

Set fWks = Worksheets("Input")
Set tWks = Worksheets("Master")

With fWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
If IsEmpty(.Cells(iRow, "A")) Then
'skip this row
Else
For iCtr = LBound(fCol) To UBound(fCol)
tWks.Range(tAddr(iCtr)).Value _
= .Cells(iRow, fCol(iCtr)).Value
Next iCtr
tWks.PrintOut preview:=True
End If
Next iRow
End With

End Sub

You can modify these two lines:

fCol = Array("b", "c", "e") 'd skipped as an example
tAddr = Array("b12", "c19", "x45")

To map the column to the address.

And I used these two lines to specify the worksheet names.

Set fWks = Worksheets("Input")
Set tWks = Worksheets("Master")

Hope this helps

"Deborah" wrote:

I have created a form in excel and an address book on a separate worksheet.
The form is a basic contract for advertising and I have a field for contract
number and then fields for the name and address of the company that contract
number belongs to. I want to be able to say "if the contract number is "x,"
populate the name and address fields with the corresponding information from
the name and address list on the second worksheet." I have tried all sorts
of tricks, but nothing is working. Any thoughts? Thank you so much.
--
Deborah

--

Dave Peterson