Cell Referencing in VBA
 

hi - Need help with understanding hwo to return a cell reference based on
values in the cell...here are some details...any help would be absolutely
appreciated.

User Input Form

Type Name Rev $ No of Stores State
Retail Reece 12 300 VIC
Retail Big W 1222 212 NSW
Retail JB 1212 232 SA
Retail Target 3234 343 WA

Database Table
Type Name Rev $ No of Stores State
Retail Reece C2 D2 C2
Retail Big W C3 D3 C3
Retail JB C4 D4 C4
Retail Target C5 D5 C5

RowCount (needs to look up the database table which has Company Type and
House Name and match this to what the userform has and get the rowcount to
point to a cell reference C2 when Company Type = "Retail" And Company Name =
"Reece"

Once i can get that cell reference i can then use offset to place the output
from userform to the table

..Offset(RowCount, 3).Value = Me.Rev$.Value
..Offset(RowCount, 4).Value = Me.NoOfStores.Value
..Offset(RowCount, 5).Value = Me.State.Value

Cell Referencing in VBA
 

Because you have a double lookup I like to make my own search macro

InputRowCount = 2 ' skip header
DatabaseRowCount = 2 ' skip header
with sheets("Input form")
do while .Range("A" & InputRowCount) < ""
Mytype = .Range("A" & InputRowCount)
Myname = .Range("A" & InputRowCount)
with sheets("Database table")
found = false
do while .Range("A" & DatabaseRowCount) < ""
if MyType = .Range("A" & DatabaseRowCount) and _
MyName = .Range("B" & DatabaseRowCount) then

found = true
exit do

end if
DatabaseRowCount = DatabaseRowCount + 1
loop
end with
if found = true then
'enter your code here
end if
InputRowCount = InputRowCount + 1
loop

end with


"amit" wrote:

hi - Need help with understanding hwo to return a cell reference based on
values in the cell...here are some details...any help would be absolutely
appreciated.

User Input Form

Type Name Rev $ No of Stores State
Retail Reece 12 300 VIC
Retail Big W 1222 212 NSW
Retail JB 1212 232 SA
Retail Target 3234 343 WA

Database Table
Type Name Rev $ No of Stores State
Retail Reece C2 D2 C2
Retail Big W C3 D3 C3
Retail JB C4 D4 C4
Retail Target C5 D5 C5

RowCount (needs to look up the database table which has Company Type and
House Name and match this to what the userform has and get the rowcount to
point to a cell reference C2 when Company Type = "Retail" And Company Name =
"Reece"

Once i can get that cell reference i can then use offset to place the output
from userform to the table

.Offset(RowCount, 3).Value = Me.Rev$.Value
.Offset(RowCount, 4).Value = Me.NoOfStores.Value
.Offset(RowCount, 5).Value = Me.State.Value

Cell Referencing in VBA
 

hi Joel - Thx a lot for this......only one issue was that i wasnt clear in my
earliar note....the user input was from a user form with combo n text
boxes....and u'r code assumed it was from a sheet......

but i have worked this out and now my code uses the user entries in the
userform as the input for the search code....

thanks again.....



"Joel" wrote:

Because you have a double lookup I like to make my own search macro

InputRowCount = 2 ' skip header
DatabaseRowCount = 2 ' skip header
with sheets("Input form")
do while .Range("A" & InputRowCount) < ""
Mytype = .Range("A" & InputRowCount)
Myname = .Range("A" & InputRowCount)
with sheets("Database table")
found = false
do while .Range("A" & DatabaseRowCount) < ""
if MyType = .Range("A" & DatabaseRowCount) and _
MyName = .Range("B" & DatabaseRowCount) then

found = true
exit do

end if
DatabaseRowCount = DatabaseRowCount + 1
loop
end with
if found = true then
'enter your code here
end if
InputRowCount = InputRowCount + 1
loop

end with


"amit" wrote:

hi - Need help with understanding hwo to return a cell reference based on
values in the cell...here are some details...any help would be absolutely
appreciated.

User Input Form

Type Name Rev $ No of Stores State
Retail Reece 12 300 VIC
Retail Big W 1222 212 NSW
Retail JB 1212 232 SA
Retail Target 3234 343 WA

Database Table
Type Name Rev $ No of Stores State
Retail Reece C2 D2 C2
Retail Big W C3 D3 C3
Retail JB C4 D4 C4
Retail Target C5 D5 C5

RowCount (needs to look up the database table which has Company Type and
House Name and match this to what the userform has and get the rowcount to
point to a cell reference C2 when Company Type = "Retail" And Company Name =
"Reece"

Once i can get that cell reference i can then use offset to place the output
from userform to the table

.Offset(RowCount, 3).Value = Me.Rev$.Value
.Offset(RowCount, 4).Value = Me.NoOfStores.Value
.Offset(RowCount, 5).Value = Me.State.Value