• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2237
  • Last Modified:

VB.net Excel cell - assign to variable

Surely this should be easy. I have an Excel worksheet and simply want to assign the cell value to a double variable, like this

double value
value = inputsWorksheet.Range(15, col).Value

I get the message
A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in Test.exe
   at Excel._Worksheet.get_Range(Object Cell1, Object Cell2)
   at TestConnectExcel.Form1.Button2_Click_1(Object sender, EventArgs e)

This is a migrate from VBA to VB.net - VBA seems easy to assign one cell value to another but I can't even do that! How does this translate?

A second matter

Sheets("output").Cells(74, 3).Value = Sheets("Input sheet").Cells(15, col).Value + Sheets("Input sheet").Cells(108, col).Value - Sheets("Input sheet").Cells(45, col).Value - Sheets("Input sheet").Cells(76, col).Value

Thanks
0
rwallacej
Asked:
rwallacej
  • 3
  • 2
1 Solution
 
bruintjeCommented:
Hi rwallacej,
----------

you need to use cells
double value
value = inputsWorksheet.Cells(15, col).Value

Range object expects 2 cells as parameters and since you're only setting a single cell .Cell(Row,Col) is the object you're looking for

the second part i didn't follow
----------
bruintje
share what you know, learn what you don't
0
 
rwallacejAuthor Commented:
sorry for confusion with 2nd part - type this too quick

how does the following VBA code get changed into VB.net

Sheets("output").Cells(74, 3).Value = Sheets("Input sheet").Cells(15, col).Value + Sheets("Input sheet").Cells(108, col).Value - Sheets("Input sheet").Cells(45, col).Value - Sheets("Input sheet").Cells(76, col).Value

0
 
rwallacejAuthor Commented:
the code above is repeated in a for loop

For col = 11 To 22 ...


next col
0
 
bruintjeCommented:
assuming you have your inputs and outputs worksheets declared


For col = 11 To 22
    outputsWorksheet.Cells(74, 3).Value = inputsWorksheet.Cells(15, col).Value + inputsWorksheet.Cells(108, col).Value - inputsWorksheet.Cells(45, col).Value - inputsWorksheet.Cells(76, col).Value
Next col
0
 
rwallacejAuthor Commented:
I'm getting errors to say "Option strict on disallows late binding"

I've got around it by assigning
i = inputsWorksheet.Cells(15, col)
j = inputsWorksheet.Cells(108, col).Value
k = ..
l = ..

answer = i + j - k - l

then  outputsWorksheet.Cells(74, 3).Value  = answer

thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now