column of entry for textbox value

[robhargreaves]

Hi I am having trouble with my userform. I have 5 controls on the
form.

a calendar (calendar1)
2 combos (cbosite & cbometerno)
1 textbox (txtreading)
1 command button (cmdaddrecord)

The user selects a value in the calendar and this is added to the first
available row in the sheet.

cbosite has a selection of all the sheet names in the workbook. The
selection here decides which sheet the calendars input and the
txtreading is printed to.

up to now I have this part working. I choose a date, select a sheet
name from cbosite and click cmdaddrecord. This activates the relevant
sheet and enters the date in the first blank row in column a as the
code asks.

The part I dont know how to do is to take the row number from where
calendar 1 has just entered and put it on one side to use as the row
number to enter txtreading.

To find the column for txtreading I need to some how cross reference
the choice in cbometerno which will be found anywhere between B1:K1 and
take the column letter.

Once I have this I know that txtreading should be entered in row x and
column y.

I have tried to explain that as well as I can but I have attached a
copy of the sheet to this post if you want to have a look.

Thanks for your time having a look.

Rob


+-------------------------------------------------------------------+
|Filename: excelproject.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3619 |
+-------------------------------------------------------------------+

 

[STEVE BELL]

If I understand you
x = row number
y = column number

Than use: Cells(x,y)
instead of Range("A1")

make sure x & y are Dim as Long.

general statement include workbook, sheet, and range designater's

WorkBooks("MyBook").Sheets("MySheet").Cells(x,y) = "My Text"

--
steveB

Remove "AYN" from email to respond
"robhargreaves" <[email protected]>
wrote in message
news:[email protected]...

 

[robhargreaves]

Thanks steve I have got some code like this but it doesnt work.

Can you comment?

Private Sub cmdaddrecord_Click()

Dim R As Range, C As Integer
Set R = Range("B1:K1").Find(cbometerno)

ActiveWorkbook.Sheets(Mid(cbosite.Text, 2, Len(cbosite.Text) -
2)).Activate

Range("A14").End(xlDown).Offset(1, 0).Value = Calendar1.Value

'if a match is found
If Not R Is Nothing Then C = R.Column 'which will return the column
number

Cells(15, C).Value = txtreading.Value

Unload Me

End Sub

 

[STEVE BELL]

Looks pretty straight forward.

put a breakpoint at the line Set R
and run the form. This should get you back to the code and you can
check variable values as you step through it.

instead of cbosite.text
try cbosite.value

this part may be the major offender

If Not R Is Nothing Then C = R.Column 'which will return the column
number

Cells(15, C).Value = txtreading.Value

change it to
If Not R Is Nothing Then
C = R.Column 'which will return the column number
Cells(15, C).Value = txtreading.Value
End If

Let me know if this helps...
--
steveB

Remove "AYN" from email to respond
"robhargreaves" <[email protected]>
wrote in message