How do you write a formula so that the cell reference corresponding to the LARGEST function can be found. For example, if cells A1 to A5 has 5, 3, 9, 1, 7 and say cell B1 has the formula =largest(A1:A5, 1), then it will show the value 9. But how do you write a formula in cell C1 so that 9 corresponds to cell A3 or say 3 is shown. apieris@raritanval.edu
I was writing a code and got it to work properly on a small scale and then spent a large amount of time making it work on a larger scale.... and then.... I got a 'procedure too large' error. I would ideally like to have everything under one command button, but I guess I can split my code up into other buttons, but it would not be as convenient for the user. Here is what my code would look like with 1 Tank case:
Code:
Private Sub CommandButton2_Click()
Dim myMonth As String
Dim myTank As String
myMonth = Worksheets("Input").Range("B6")
myTank = Worksheets("Input").Range("C6")
Application.ScreenUpdating = False
Select Case myTank
Case "1"
Select Case myMonth
Case "Jan"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K6").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D6").PasteSpecial _
Paste:=xlPasteValues
Case "Feb"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K7").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D7").PasteSpecial _
Paste:=xlPasteValues
Case "Mar"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K8").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D8").PasteSpecial _
Paste:=xlPasteValues
Case "Apr"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K9").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D9").PasteSpecial _
Paste:=xlPasteValues
Case "May"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K10").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D10").PasteSpecial _
Paste:=xlPasteValues
Case "Jun"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K11").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D11").PasteSpecial _
Paste:=xlPasteValues
Case "Jul"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K12").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D12").PasteSpecial _
Paste:=xlPasteValues
Case "Aug"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K13").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D13").PasteSpecial _
Paste:=xlPasteValues
Case "Sep"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K14").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D14").PasteSpecial _
Paste:=xlPasteValues
Case "Oct"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K15").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D15").PasteSpecial _
Paste:=xlPasteValues
Case "Nov"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K16").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D16").PasteSpecial _
Paste:=xlPasteValues
Case "Dec"
Worksheets("Tankcalculations").Range("E17:AJ17").Copy
Worksheets("Output").Range("K17").PasteSpecial _
Paste:=xlPasteValues
Worksheets("Input").Range("B6:H6").Copy
Worksheets("Output").Range("D17").PasteSpecial _
Paste:=xlPasteValues
End Select
End Select
Application.ScreenUpdating = True
MsgBox "Input Successful"
End Sub
Now, I have 75 Tanks that I would like to do this copy paste operation for. Is there any way I can bring it to a manageable size and keep it on one button?
Hello all,
First post here. I've done some searching on Google and couldn't find the answer I was looking for. I'll preface my question by stating that I have no knowledge of how to create macros and my knowledge of Excel is somewhat basic.
I'm collecting data for an experiment I'm running. It's recording force vs. displacement about 20 times per second for about 3-5 minutes. This gives me a LOT of data points. The only very important data points are where something "interesting" happens, such as a sharp drop in force or a significant change in slope.
I have about 20 sheets in my workbook, each with about 10,000 to 30,000 points of data which need to be graphed to be analyzed. Is there any easy way that I could thin out the data in the "uninteresting" sections such as the parts that are very linear?
If that's not possible, how would I go about just taking about every 10th data point and putting that in a new sheet?
Thanks
-jz
I need to display the first, second and third in an array of percentages. I
tried using the 'Statistical Function' 'Large', first by using 'Insert
Function', selecting 'Large' then entering the Array [F11:F100], then the
order-value K [3]. The Formula-Bar displayed =LARGE(F11:F100 3)and when I
pressed Enter it returned an error-window, stating 'too few arguments' were
entered. I then tried entering the Formula directly [using the syntax
suggested in 'Help' (by copy and paste)]. The Formula-Bar displayed
=LARGE(F11:F100,3)and when I pressed Enter it again returned an error-window,
stating 'too few arguments' were entered. Neither of these [very similar]
syntaxes worked! Please help!
Hi,
I have an Excel Sheet with some questions on it and I send this this worksheets with VBA code which works in MS Excel but when you put this file on a webpage the Thisworkbook.SaveCopyAs mypath & myfilename debugs. The code is below.
Code:
Sub SendMail()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Managername
Dim OutlookErr, OutlookBox
Dim myOlApp As Object
Dim olMailItem
Dim myMail As Object
myPath = Application.DefaultFilePath & "\"
Set ThisBook = ThisWorkbook
myFilename = Left(ThisBook.Name, Len(ThisBook.Name) - 4) & "_" & Day(Date) & "_" & MonthName(Month(Date), True) & "_" & Year(Date) & "_" & Hour(Time()) & "_" & Minute(Time()) & "_" & Second(Time()) & ".xls"
ThisBook.SaveCopyAs myPath & myFilename ' ** THIS IS THE OFFENDING LINE ****
Application.DisplayAlerts = False
Set myOlApp = CreateObject("Outlook.Application") 'Create outlook
Set myMail = myOlApp.CreateItem(olMailItem) 'create mail item
With myMail
.Display
.To = ("Kuldip.mond@test.com")
.Subject = "Online Form Submitted " & ThisWorkbook.Name
.body = "Form Submitted "
.Attachments.Add (myPath & myFilename)
End With
AppActivate (myMail)
SendKeys ("^~")
Kill (myPath & myFilename) 'delete temporary file
Application.ScreenUpdating = True
MsgBox "Your message has been sent."
Set myOlApp = Nothing 'unset variable
Set myMail = Nothing 'unset variable
End Sub
Basically I think the problem is that the version of Excel it uses in Internet Explorer does not have the same components as in MS Excel launched via your PC.
When I take the Thisworkbook.SaveCopyAs this works but as you can appreciate it does not send the file and the routine does not do what its supposed to.
Anyway, does anyone know of a way around this, as the problems are many because I am trying to send a copy of the current workbook.
I have also tried using Workbooks.add but this also causes the same problem.
Many Thanks in Advance. Kuldip
p.s. using 2003
Morning all,
I have a problem with the userform.show command line.
This was working fine until yesterday and I have a feeling it has to do with my excel settings. In short every time the command line Userform1.show or UserForm2.show appears etc excel automatically opens the Visual Basics Editor and highlights the line. I have tired this on other peoples excel and it does not occur.
Any ideas on what settings I have buggered up would be helpful.
thanks
I would enjoy learning how I could debug a VB6 dll launched from the XL environment.
In order to manage a number crunching functionality in an easier way as well as to reduce file size I decided to remote the VBA code to a VB6 dll. This work fine, and I am very happy with it.
But there is now a disadvantage: I don't know how to debug in this situation and I must maintian two versions, one for development fully within xl and the other combining XL and VB6. This implies frequently moving code and it is not confortable.
In the past, I was used to debug J++ dlls launched from XL.
But I don't see how this could be done with VB6.
Any idea?
Thanks
Hi Guys,
Does anyone know of a web site which offers Excel tips and tricks.
Is there a free book which I can download that will give me tips and tricks.
Thanking you in advance,
Regards
J-O
What is the easy way to create forms in different sheets.......
In Main Sheet (Sheet1) each row contains some informations about a particular item.
I want to create Forms in separate Sheets and datas for this Form 1 (Sheet2) should be datas of Row#1 of Sheet1 & datas for this Form 2 (Sheet2) should be datas of Row#2 of the Sheet1.......
Hi
I have an Excelsheet. Column A has names and Column B has telephone numbers with country codes. I want to click a button and it should dail the number where the pointer is placed (in a cell in Column B).
Can someone write me the macro, please ??
