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

[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]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> robhargreaves
> ------------------------------------------------------------------------
> robhargreaves's Profile:
> http://www.excelforum.com/member.php...o&userid=25519
> View this thread: http://www.excelforum.com/showthread...hreadid=389584
>

[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
news:[email protected]...
>
> 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
>
>
> --
> robhargreaves
> ------------------------------------------------------------------------
> robhargreaves's Profile:
> http://www.excelforum.com/member.php...o&userid=25519
> View this thread: http://www.excelforum.com/showthread...hreadid=389584
>