I need the variable here to be 7 numbers long, if not I need to add zero's at the beginning. However the problem I am having here is when then number of zero's is greater than one.
Basically in the example below the variable 'Zeros' = 2 so I need the variable 'Variable' to give answer "00", how do I do this!?
If Len(ActiveCell.Value) 7 Then
Zeros = 7 - Len(ActiveCell.Value)
Variable = CStr("0") * Zeros
End If
I have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?
As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.
The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.
It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.
If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.
I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.
I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.
However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....
I have used this forum periodically and it has saved my bacon on numerous occasions so I know someone can help with this!
I created a workbook for staff planning and it's becoming a behemoth!
I'm wanting a range of cells within a row to change the number format to "[hh]:mm" based on a value in the 2nd column of that row. I have played about with a few things and can't seem to get it to work. What I have so far is this:
Can I change the color of a cell based on my deviation from the number based above it? I am making a golf spreadsheet to base my performance off of, and I was wondering if I could make the cell change color based on my performance on the hole (different color for how many strokes above or under par). If anyone has any idea on this issue, please let me know.
Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'
Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.
Column AL's value is based on an IF formula which goes like this: =IF($C4="F/T",SUM($AJ4)+($AK4/2),SUM($D4:$AH4))
Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).
I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.
This spreadsheet will be tracking sales and purchases. I want to be able to have any purchases formatted as an expenditure (negative sign or brackets) to show it is money out. Column C "Action" has a drop down with either Buy (infrequent) or Sell. I want to be able to have column I "Unit Price" be a negative number if the corresponding cell in the same row in Column C = "Buy". I do not have these prices formatted as currency as their are 2 different currency used and I don't know how to make the correct currency format appear automatically, though I do have a drop down validation column for which currency the transaction was in, so maybe that is possible. I know this is probably simple, but I don't spend that much time using Excel. I have some sample data in their for the moment.
With that said, I want to put the row number of a user in (I have a few thousand on this spreadsheet, and all the = data be automatically pulled based on the row number I put in cell B1. So I'm hoping to write in cell B1 the following - "143" (without the quotes), and the remaining cells in column B automatically pull that data based on that, so it would look like the following
Row # 143 Name: =C143&" , "&D143&" "&E143
[Code]....
How do I write the functions to keep the columns the same, but change the row number based on the number I input?
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
On my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet.
To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field.
The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively.
To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............
should deliver me the same values i.e changedname 123456 and true
for testarray(0),testarray(1),testarray(2) i am getting the values but I am not able to change the variable values for custname ,custaccount and worthcredit, although I am accessing the same elements.
Trying to create a VBA code that will enter a formula such as: SUM(Q2:Qt) in a range of cells for instance A2:A10, and if there is information in B11, B12, B13 etc., it will consider that variable change and enter the SUM formula in cells A11, A12, A13.
I read that Dim LR As Long is the solution but not sure how to apply.
I have a list of Variables Dimentioned a Range. They are sequential; ie. A1, A2, A3, etc. How do I write the code to switch between these? Obviously, I am looking to correctly reference ("A" & aNo).
Code: For Test = 1 To aNo With ("A" & aNo) .Select
I have a text variable MtgDate containing "25/03/2014"
I need to produce another text variable (to build into a file name) MtgDate1 with the text "2014-03-25"
I was going to use the Substitute function to replace the "/" with a "-" then Mid to juggle the dd-mm-yyyy to yyyy-mm-dd but at the moment I can't even find the right syntax for the Substitute.
Got as far as:
[Code] .....
but this just sets MtgDate1 as "=Substitute(MtgDate, " / ", " - ") ie reduces the double quotes to single ones.
How do I change the selection of a range with a variable, and not a hardcoded number in XL2003? I have to update a set of spreadsheets every month, and it's a hassle to have to constantly open my pivot table worksheet, copy, open the summary worksheet, paste ... etc. etc. etc. The code below is my attempt at creating a ComboBox with "January, February, March, etc." and every time I select a particular month it will automatically copy data from my pivot table worksheet into my new summary worksheet in the correct column. The range of data from my pivot table worksheet will never change, so I have no problem hardcoding that in, but based on which month is selected will alter which column the data goes in in the new worksheet.
I want to be able to write code for one month (say January) and then when I want to use a different month (say February) I can just change the column number and call up the originial January code.
Private Sub ComboBox1_Change() Dim ColNum ' This is the variable I want to change based on which month is called If ComboBox1.Value = "January" Then ColNum = 1 ' Column number for Column A- Where my January column is Elseif ComboBox1.Value = "February" Then ColNum = 2 ' Column number for Column B- Where my February column is ' etc. etc. for each month End If Call January End Sub
Private Sub January() ' Just a quick msgbox to make sure the previous macro is calling this one MsgBox "Is this macro running?", vbQuestion + vbYesNo, "Check"...........................
Im trying to use an event change to change the sheet name based on a cell value, but my issue is how can I error trap if the sheet name is a duplicate? Here is what I have so far
Sub ChangeName() On Error GoTo errhandler Sheets(1).Name = Sheets(1).range("d10") Exit Sub errhandler: MsgBox "sheet name is already exists" End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Define the RMAs range and count the RMA ships. Do the rest of the math based on this calculation. Dim intRMACol As Integer, EndRow As Integer, intRMAShips As Integer, LastCol As Integer Dim rngRMAs As Range, rngHome As Range, sht As Worksheet Set sht = ActiveWorkbook.ActiveSheet Set rngHome = Cells(1, 1) rngHome.Activate EndRow = Range("A" & Rows.Count).End(xlUp).Row intRMACol = Cells.Find(What:="RMA#", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column Set rngRMAs = rngHome.Offset(1, intRMACol - 1).Resize(EndRow, 1) rngRMAs.Name = "RMAs" rngHome.Offset(1, LastCol + 1).Formula = "=COUNTA(RMAs)" intRMAShips = rngHome.Offset(1, LastCol + 1).Value rngHome.Offset(1, LastCol).Formula = "=SUM((" & EndRow & " - 1) - " & intRMAShips & ")" rngHome.Offset(1, LastCol + 2).FormulaR1C1 = "=SUM(RC[-2] : RC[-1])" End Sub
I keep getting stuck when trying to set the intRMACol variable. I have used the code all over the place, but only in Modules, never on a worksheet event. EndRow, the variable right above, sets correctly so I don't get it.
Also, do I have to define the Target variable? Am I going about this the right way? I basically want three formulas to calculate when any change is made on the worksheet.
How do you drag down a column to auto complete the values but do it so it only changes one variable.For example, I am dragging down =IF(ROW()<=$B$5,MOD(E2*B1,B2),"") down my column.
However, I only want the "E2" value to change, and I want B1 and B2 to stay that way. Whenever I drag down, it keeps changing all three values. Is there a way to make it just so that the E2 value is the only one that changes?
I am acquiring multiple spreadsheets that do not always match row number, due to additional information on some sheets. By this I mean that the information may be in row 31 on on sheet and row 39 on another, the column location is the same each time. I have been trying Vlookups, indexing and matching plus combination formulas - with no luck. How to get the information I need with a moving cell reference?
I can provide a small copy of a workbook, if needed.
