Hi all,
I just registered to this forum, sorry to start with a question.
I have written two formulas in excel and want to extend to many cells in a certain interval and generalize to other similar worksheets. So, I need to write them as vba code. The formulas:
=INDEX('sheet1'!$A2:$BI$7000,MATCH(MAX('sheet1'!I2 :I7),'sheet1'!I2:I7,0),COLUMN('sheet1'!A1))
=IF(C3=W$3,100,IF(C3<W$4,TREND($V$3:$V$4,W$3:W$4,C 3),IF(C3=W$4,85,IF(C3<W$5,TREND(V$4:V$5,W$4:W$5,C3 ),IF(C3=W$5,5,IF(C3<W$6,TREND($V$5:$V$6,W$5:W$6,C3 ),0))))))
I appreciate if any1 can help me to come up with vba codes.
thanks in advance
3 3353
dear,
I hope this will help You: - If Range("C3") = Range("W$3") Then
-
ActiveCell = 100
-
Else
-
If Range("C3") < Range("W$4") Then
-
ActiveCell.Value = WorksheetFunction.Trend("$V$3:$V$4,W$3:W$4,C3")
-
Else
-
If Range("C3") = Range("W$4") Then
-
ActiveCell = 85
-
Else
-
If Range("C3") < Range("W$5") Then
-
ActiveCell.Value = WorksheetFunction.Trend("V$4:V$5,W$4:W$5,C3")
-
Else
-
If Range("C3") = Range("W$5") Then
-
ActiveCell = 5
-
Else
-
If Range("C3") < Range("W$6") Then
-
ActiveCell.Value = WorksheetFunction.Trend("$V$5:$V$6,W$5:W$6,C3")
-
Else
-
ActiveCell = 0
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
Question:
Range("...") is allways a fix cell (even with or without $).
The input data cells don't' move with the move of the calculation to an other cell.
If you want to execute the formula to a range of cells, you have also to readdress the datacells with "offset"
Is it possible to attach a example of the sheet?
br,
dear,
IF's on a same item can also be writen as SELECT CASE like this: - Select Case Range("C3")
-
Case Is = Range("W$3")
-
ActiveCell = 100
-
Case Is < Range("W$4")
-
ActiveCell = WorksheetFunction.Trend("$V$3:$V$4,W$3:W$4,C3")
-
Case Is = Range("W$4")
-
ActiveCell = 85
-
Case Is < Range("W$5")
-
ActiveCell = WorksheetFunction.Trend("V$4:V$5,W$4:W$5,C3")
-
Case Is = Range("W$5")
-
ActiveCell = 5
-
Case Is < Range("W$6")
-
ActiveCell = WorksheetFunction.Trend("$V$5:$V$6,W$5:W$6,C3")
-
Case Else
-
ActiveCell = 0
-
End Select
br,
dear,
is this a solution? - Dim INDEXrange As Range
-
Dim MATCHrange As Range
-
Dim MAXrange As Range
-
Dim COLUMNrange As Range
-
-
Set INDEXrange = Worksheets("sheet1").Range("A2:$BI$7000")
-
Set MATCHrange = Worksheets("sheet1").Range("I2:$I$7,0")
-
Set MAXrange = Worksheets("sheet1").Range("I2:I7")
-
Set COLUMNrange = Worksheets("sheet1").Range("A1")
-
-
ActiveCell = WorksheetFunction.Index(INDEXrange, _
-
WorksheetFunction.Match( _
-
WorksheetFunction.Max(MAXrange), _
-
MATCHrange), _
-
COLUMNrange)
br,
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Don.Vonderburg |
last post by:
I am having a problem importing an Excel spreadsheet. I have a column in
an Excel sheet with alphanumeric text and some of the cells are numeric.
Some of the cells contain numbers like 12345.6 and...
|
by: Kumar |
last post by:
Hi Folks,
I have a question regarding my windows c# application.
This application just reads MS Excel file and puts the data in to sql server
database.
In that excel file ,it has one named cell...
|
by: Carlos Magalhaes |
last post by:
Hey All,
I am doing some excel automation using the excel COM. I can do most of
the functions and its working well until I come across a formula.
I can run a formula and insert the formula...
|
by: Rich Wallace |
last post by:
Hi all,
I have a VB app that runs and manages individual XLS files within a single
COM object. Upon processing the final fie, I attempt to close out the EXCEL
object and release it using...
|
by: Esmael |
last post by:
Hi to all...
Goodmorning
am working on a Project on VB6. THis is an excel automation... the VB6 Program reads from the Excel File and transfer the search data if found to another excel file......
|
by: toffee |
last post by:
Hi all,
I got a pre-formatted spreadsheet. would it be possible using js to copy the
data from a table on the current webpage, open the spreadsheet and paste the
content ?
if so, anyone got any...
|
by: alphaomega3 |
last post by:
I have a formula used in Excel that I am trying to convert for use in a query. the original formula is:
...
|
by: barnzee |
last post by:
Hi all, newbie here, but having a go
I am trying to build a stock watchlist in excel 2007 with a dynamic link to a DDE server (paid for from a broker).There is no add-in or plug-in, I just CTL ALT...
|
by: sejal17 |
last post by:
hello
Can any one tell me how to read multiple worksheets from a single excel file.I have stored that excel in xml file.so i want to read that xml that has multiple worksheet.And i want to store...
|
by: sejal17 |
last post by:
hello
Can any one tell me how to read multiple worksheets from a single excel file.I have stored that excel in xml file.so i want to read that xml that has multiple worksheet.And i want to store...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |