Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > help with code for scientific worksheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default help with code for scientific worksheet
I've written the following code, with comments, to work in a workbook for
calculating values for scientific pipettors. However, when I run the code, I
get an error that says type mismatch. Am I missing something? Do I need to
declare the H5 cell specifically as a double or something? Help!

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for the
input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to d11 if
the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named Pipettor in
cells J9 to L11.

If [pipettor_calibration_worksheet.h5] = 0.2 Then
[pipettor.j9:l11] = [errors.b9:d11]
End If
If [pipettor_calibration_worksheet.h5] = 0.5 Then
[pipettor.j9:l11] = [errors.b13:d15]
End If
If [pipettor_calibration_worksheet.h5] = 1 Then
[pipettor.j9:l11] = [errors.b17:d19]
End If
If [pipettor_calibration_worksheet.h5] = 2 Then
[pipettor.j9:l11] = [errors.b21:d23]
End If
If [pipettor_calibration_worksheet.h5] = 10 Then
[pipettor.j9:l11] = [errors.b25:d27]
End If
If [pipettor_calibration_worksheet.h5] = 20 Then
[pipettor.j9:l11] = [errors.b29:d31]
End If
If [pipettor_calibration_worksheet.h5] = 100 Then
[pipettor.j9:l11] = [errors.b33:d35]
End If
If [pipettor_calibration_worksheet.h5] = 500 Then
[pipettor.j9:l11] = [errors.b37:d39]
End If
If [pipettor_calibration_worksheet.h5] = 1000 Then
[pipettor.j9:l11] = [errors.b41:d43]
End If
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 79
Default help with code for scientific worksheet
On 15 Ago, 01:28, Anne wrote:
I've written the following code, with comments, to work in a workbook for
calculating values for scientific pipettors. However, when I run the code, I
get an error that says type mismatch. Am I missing something? Do I need to
declare the H5 cell specifically as a double or something? Help!

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for the
input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to d11 if
the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named Pipettor in
cells J9 to L11.

If [pipettor_calibration_worksheet.h5] = 0.2 Then
[pipettor.j9:l11] = [errors.b9:d11]
End If
If [pipettor_calibration_worksheet.h5] = 0.5 Then
[pipettor.j9:l11] = [errors.b13:d15]
End If
If [pipettor_calibration_worksheet.h5] = 1 Then
[pipettor.j9:l11] = [errors.b17:d19]
End If
If [pipettor_calibration_worksheet.h5] = 2 Then
[pipettor.j9:l11] = [errors.b21:d23]
End If
If [pipettor_calibration_worksheet.h5] = 10 Then
[pipettor.j9:l11] = [errors.b25:d27]
End If
If [pipettor_calibration_worksheet.h5] = 20 Then
[pipettor.j9:l11] = [errors.b29:d31]
End If
If [pipettor_calibration_worksheet.h5] = 100 Then
[pipettor.j9:l11] = [errors.b33:d35]
End If
If [pipettor_calibration_worksheet.h5] = 500 Then
[pipettor.j9:l11] = [errors.b37:d39]
End If
If [pipettor_calibration_worksheet.h5] = 1000 Then
[pipettor.j9:l11] = [errors.b41:d43]
End If
End Sub

Hi Anne.
I believe not only type mismatch, however try with this scheme:

'-------
If [pipettor_calibration_worksheet].[h5] = 0.2 Then
[Errors].[b9:d11].Copy Destination:=[pipettor].[j9:l11]
ElseIf [pipettor_calibration_worksheet].[h5] = 0.5 Then
[Errors].[b13:d15].Copy Destination:=[pipettor].[j9:l11]
elseif........................
................................
elseif.......................
................................
elseif.......................
...............................
else
'do nothing
end if
..................
.................
end sub

and be siure to calibrate correctrly your pipettors.:-))
Regards
Eliano
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default help with code for scientific worksheet
Hi Anne

Try to replace the does with exclamation signs:

If [pipettor_calibration_worksheet!h5] = 0.2 Then

Or try the macro below:

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for
the input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to
d11 if the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named
Pipettor in cells J9 to L11.
Dim Dest As Range

Set DestCell = Sheets("pipettor").Range("j9")
Select Case Sheets("pipettor_calibration_worksheet").Range("h5 ")
Case Is = 0.2
Sheets("errors").Range("b9:d11").Copy DestCell
Case Is = 0.5
Sheets("errors").Range("b13:d15").Copy DestCell
Case Is = 1
Sheets("errors").Range("b17:d19").Copy DestCell
Case Is = 2
Sheets("errors").Range("b21:d23").Copy DestCell
Case Is = 10
Sheets("errors").Range("b25:d27").Copy DestCell
Case Is = 20
Sheets("errors").Range("b29:d31").Copy DestCell
Case Is = 100
Sheets("errors").Range("b33:D35").Copy DestCell
Case Is = 500
Sheets("errors").Range("b37:d39").Copy DestCell
Case Is = 1000
Sheets("errors").Range("b41:d43").Copy DestCell
End Select
End Sub


On 15 Aug., 01:28, Anne wrote:
I've written the following code, with comments, to work in a workbook for
calculating values for scientific pipettors. However, when I run the code, I
get an error that says type mismatch. Am I missing something? Do I need to
declare the H5 cell specifically as a double or something? Help!

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for the
input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to d11 if
the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named Pipettor in
cells J9 to L11.

If [pipettor_calibration_worksheet.h5] = 0.2 Then
[pipettor.j9:l11] = [errors.b9:d11]
End If
If [pipettor_calibration_worksheet.h5] = 0.5 Then
[pipettor.j9:l11] = [errors.b13:d15]
End If
If [pipettor_calibration_worksheet.h5] = 1 Then
[pipettor.j9:l11] = [errors.b17:d19]
End If
If [pipettor_calibration_worksheet.h5] = 2 Then
[pipettor.j9:l11] = [errors.b21:d23]
End If
If [pipettor_calibration_worksheet.h5] = 10 Then
[pipettor.j9:l11] = [errors.b25:d27]
End If
If [pipettor_calibration_worksheet.h5] = 20 Then
[pipettor.j9:l11] = [errors.b29:d31]
End If
If [pipettor_calibration_worksheet.h5] = 100 Then
[pipettor.j9:l11] = [errors.b33:d35]
End If
If [pipettor_calibration_worksheet.h5] = 500 Then
[pipettor.j9:l11] = [errors.b37:d39]
End If
If [pipettor_calibration_worksheet.h5] = 1000 Then
[pipettor.j9:l11] = [errors.b41:d43]
End If
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default help with code for scientific worksheet
Thanks, Per and Eliano! The CASE statements worked out great!
Anne

"Per Jessen" wrote:

Hi Anne

Try to replace the does with exclamation signs:

If [pipettor_calibration_worksheet!h5] = 0.2 Then

Or try the macro below:

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for
the input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to
d11 if the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named
Pipettor in cells J9 to L11.
Dim Dest As Range

Set DestCell = Sheets("pipettor").Range("j9")
Select Case Sheets("pipettor_calibration_worksheet").Range("h5 ")
Case Is = 0.2
Sheets("errors").Range("b9:d11").Copy DestCell
Case Is = 0.5
Sheets("errors").Range("b13:d15").Copy DestCell
Case Is = 1
Sheets("errors").Range("b17:d19").Copy DestCell
Case Is = 2
Sheets("errors").Range("b21:d23").Copy DestCell
Case Is = 10
Sheets("errors").Range("b25:d27").Copy DestCell
Case Is = 20
Sheets("errors").Range("b29:d31").Copy DestCell
Case Is = 100
Sheets("errors").Range("b33:D35").Copy DestCell
Case Is = 500
Sheets("errors").Range("b37:d39").Copy DestCell
Case Is = 1000
Sheets("errors").Range("b41:d43").Copy DestCell
End Select
End Sub


On 15 Aug., 01:28, Anne wrote:
I've written the following code, with comments, to work in a workbook for
calculating values for scientific pipettors. However, when I run the code, I
get an error that says type mismatch. Am I missing something? Do I need to
declare the H5 cell specifically as a double or something? Help!

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for the
input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to d11 if
the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named Pipettor in
cells J9 to L11.

If [pipettor_calibration_worksheet.h5] = 0.2 Then
[pipettor.j9:l11] = [errors.b9:d11]
End If
If [pipettor_calibration_worksheet.h5] = 0.5 Then
[pipettor.j9:l11] = [errors.b13:d15]
End If
If [pipettor_calibration_worksheet.h5] = 1 Then
[pipettor.j9:l11] = [errors.b17:d19]
End If
If [pipettor_calibration_worksheet.h5] = 2 Then
[pipettor.j9:l11] = [errors.b21:d23]
End If
If [pipettor_calibration_worksheet.h5] = 10 Then
[pipettor.j9:l11] = [errors.b25:d27]
End If
If [pipettor_calibration_worksheet.h5] = 20 Then
[pipettor.j9:l11] = [errors.b29:d31]
End If
If [pipettor_calibration_worksheet.h5] = 100 Then
[pipettor.j9:l11] = [errors.b33:d35]
End If
If [pipettor_calibration_worksheet.h5] = 500 Then
[pipettor.j9:l11] = [errors.b37:d39]
End If
If [pipettor_calibration_worksheet.h5] = 1000 Then
[pipettor.j9:l11] = [errors.b41:d43]
End If
End Sub


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default help with code for scientific worksheet
Thanks for your reply. I am glad to help.
Per

On 15 Aug., 15:26, Anne wrote:
Thanks, Per and Eliano! The CASE statements worked out great!
Anne



"Per Jessen" wrote:
Hi Anne

Try to replace the does with exclamation signs:

If [pipettor_calibration_worksheet!h5] = 0.2 Then

Or try the macro below:

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for
the input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to
d11 if the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named
Pipettor in cells J9 to L11.
Dim Dest As Range

Set DestCell = Sheets("pipettor").Range("j9")
Select Case Sheets("pipettor_calibration_worksheet").Range("h5 ")
Case Is = 0.2
* * Sheets("errors").Range("b9:d11").Copy DestCell
Case Is = 0.5
* * Sheets("errors").Range("b13:d15").Copy DestCell
Case Is = 1
* * Sheets("errors").Range("b17:d19").Copy DestCell
Case Is = 2
* * Sheets("errors").Range("b21:d23").Copy DestCell
Case Is = 10
* * Sheets("errors").Range("b25:d27").Copy DestCell
Case Is = 20
* * Sheets("errors").Range("b29:d31").Copy DestCell
Case Is = 100
* * Sheets("errors").Range("b33:D35").Copy DestCell
Case Is = 500
* * Sheets("errors").Range("b37:d39").Copy DestCell
Case Is = 1000
* * Sheets("errors").Range("b41:d43").Copy DestCell
End Select
End Sub

On 15 Aug., 01:28, Anne wrote:
I've written the following code, with comments, to work in a workbook for
calculating values for scientific pipettors. However, when I run the code, I
get an error that says type mismatch. Am I missing something? Do I need to
declare the H5 cell specifically as a double or something? Help!

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for the
input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to d11 if
the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named Pipettor in
cells J9 to L11.

If [pipettor_calibration_worksheet.h5] = 0.2 Then
[pipettor.j9:l11] = [errors.b9:d11]
End If
If [pipettor_calibration_worksheet.h5] = 0.5 Then
[pipettor.j9:l11] = [errors.b13:d15]
End If
If [pipettor_calibration_worksheet.h5] = 1 Then
[pipettor.j9:l11] = [errors.b17:d19]
End If
If [pipettor_calibration_worksheet.h5] = 2 Then
[pipettor.j9:l11] = [errors.b21:d23]
End If
If [pipettor_calibration_worksheet.h5] = 10 Then
[pipettor.j9:l11] = [errors.b25:d27]
End If
If [pipettor_calibration_worksheet.h5] = 20 Then
[pipettor.j9:l11] = [errors.b29:d31]
End If
If [pipettor_calibration_worksheet.h5] = 100 Then
[pipettor.j9:l11] = [errors.b33:d35]
End If
If [pipettor_calibration_worksheet.h5] = 500 Then
[pipettor.j9:l11] = [errors.b37:d39]
End If
If [pipettor_calibration_worksheet.h5] = 1000 Then
[pipettor.j9:l11] = [errors.b41:d43]
End If
End Sub- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default help with code for scientific worksheet
The OP should definitely use your Select Case construction... there is no
argument over that. However, it is a weekend, things are a little slow on
the newsgroups and I was a little bored, so I decided to try and reduce your
macro's code down to a single line of code... just for the fun of it<g.
Here is the results of that effort...

Sub PipettorValueFillIn()
Sheets("errors").Range("B9:D11").Offset((4 * (InStr( _
"00.2 00.5 0001 0002 0010 0020 0100 0500 1000", _
Right("000" & Sheets("pipettor_calibration_worksheet"). _
Range("h5"), 4)) - 1) \ 5)).Copy Sheets("pipettor").Range("j9")
End Sub

--
Rick (MVP - Excel)


"Per Jessen" wrote in message
...
Hi Anne

Try to replace the does with exclamation signs:

If [pipettor_calibration_worksheet!h5] = 0.2 Then

Or try the macro below:

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for
the input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to
d11 if the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named
Pipettor in cells J9 to L11.
Dim Dest As Range

Set DestCell = Sheets("pipettor").Range("j9")
Select Case Sheets("pipettor_calibration_worksheet").Range("h5 ")
Case Is = 0.2
Sheets("errors").Range("b9:d11").Copy DestCell
Case Is = 0.5
Sheets("errors").Range("b13:d15").Copy DestCell
Case Is = 1
Sheets("errors").Range("b17:d19").Copy DestCell
Case Is = 2
Sheets("errors").Range("b21:d23").Copy DestCell
Case Is = 10
Sheets("errors").Range("b25:d27").Copy DestCell
Case Is = 20
Sheets("errors").Range("b29:d31").Copy DestCell
Case Is = 100
Sheets("errors").Range("b33:D35").Copy DestCell
Case Is = 500
Sheets("errors").Range("b37:d39").Copy DestCell
Case Is = 1000
Sheets("errors").Range("b41:d43").Copy DestCell
End Select
End Sub


On 15 Aug., 01:28, Anne wrote:
I've written the following code, with comments, to work in a workbook for
calculating values for scientific pipettors. However, when I run the
code, I
get an error that says type mismatch. Am I missing something? Do I need
to
declare the H5 cell specifically as a double or something? Help!

Sub PipettorValueFillIn()
'Based on the value typed into cell H5 on the sheet
Pipettor_Calibration_Worksheet,
'this module will look up and return the volume, percent and CV for the
input value.
'Volume, percent and CV are in the sheet named errors in cells b9 to d11
if
the vallue is 0.2, for example.
'The volume, percent and CV should be placed in the sheet named Pipettor
in
cells J9 to L11.

If [pipettor_calibration_worksheet.h5] = 0.2 Then
[pipettor.j9:l11] = [errors.b9:d11]
End If
If [pipettor_calibration_worksheet.h5] = 0.5 Then
[pipettor.j9:l11] = [errors.b13:d15]
End If
If [pipettor_calibration_worksheet.h5] = 1 Then
[pipettor.j9:l11] = [errors.b17:d19]
End If
If [pipettor_calibration_worksheet.h5] = 2 Then
[pipettor.j9:l11] = [errors.b21:d23]
End If
If [pipettor_calibration_worksheet.h5] = 10 Then
[pipettor.j9:l11] = [errors.b25:d27]
End If
If [pipettor_calibration_worksheet.h5] = 20 Then
[pipettor.j9:l11] = [errors.b29:d31]
End If
If [pipettor_calibration_worksheet.h5] = 100 Then
[pipettor.j9:l11] = [errors.b33:d35]
End If
If [pipettor_calibration_worksheet.h5] = 500 Then
[pipettor.j9:l11] = [errors.b37:d39]
End If
If [pipettor_calibration_worksheet.h5] = 1000 Then
[pipettor.j9:l11] = [errors.b41:d43]
End If
End Sub


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does ker_01 Excel Programming 6 October 3rd 08 09:45 PM
How to get variables from worksheet code to userform code Donny Excel Programming 6 August 23rd 08 01:31 AM
how to prevent code running when in a worksheet code Corey Excel Programming 5 August 13th 06 08:52 AM
Code Conflicts With Worksheet Change Code Paige Excel Programming 3 March 3rd 06 04:25 PM
Create a newworksheet with VBA code and put VBA code in the new worksheet module ceshelman Excel Programming 4 June 15th 05 04:37 PM


All times are GMT +1. The time now is 04:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement