Here is what I try to do:
At
A1 = Power(C1,B1)
A2 = Power(C2, B1)
A3 = Power(C3, B1)
....
An = Power(Cn, B1)
How can I increment column C while keeping B constant at B1 by using right-bottom mouse pointer dragging on column A? Or is there another way?
I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.
Code for Form2: Const myName As String = Form1.txtName.Value
I have a number of strings in adjacent columns in the same row, like so.
Coulmn1 Column2 "blank" Column3
If the cell is blank I want to hide the respective column .
I'm guessing the code will roughly take the form of the code below with an if statement nested in a for statement.
Sub (test)
With Range("BD22")
For i = 1 To 10
if range (i,22) = "" then hide
else don't hide
.Offset(1, 0).Formula
Next i
End With
I have a workbook that has a few data sheets and a summary sheet. On this summary sheet I have to grab data from the data sheets which is in the same row number but different column (always 5 to the right of the previous one).
Eg: Person's Name is in Row 10, Col E (E10), the next name is in J10, then O10, etc. This carries on past Column Z. For each person there is a bunch of other data which is in the same format (set row but 5 over for column).
Since row stays the same i thought i could absolute reference that and then leave column as relative and drag down. This works but only if I drag to the right (across columns) and only if i need to increment by 1.
I have a worksheet (Sheet1) with column A labeled MissionNumber. I have also created a Form which I can use to enter the next mission number into that column. However, instead of manually entering the number...when opening the form I would like it to check column A for the last entry...and automatically increment the mission number by 1 and have it displayed on the form.
View 10 Replies View RelatedHave table with five columns and five rows. The columns(C4:H4) hold Systems, Systems is a defined range, and rows (B5:B9) hold staffer names, Staffer is a defined range. Cell A5 holds period, by quarters, ie, Q1-Q4.
At present am using =COUNTIFS(Staffer,$B$5,System,C4,Q,$A$5). This works fine but as you can see I must change the Staffer starting reference $B$5, with each new row.
formula that changes column and row automatically as I drag down and across.
I'm using a macro to update individual formulas in a sheet that contains data from external documents. (i.e. ='C:September[Book2.xls]Sheet1'!$AZ$1 + 'C:September[Book3.xls]Sheet1'!$BA$1 ) Every quarter I have to change the column references in this formula (i.e. $AZ$ will become $BD$ and $BA$ will become $BE$ and so on). Also I have to change the September reference to December etc (but I've mastered this already). My current code works well, but requires manual updating:
Dim ColumnReference1
ColumnReference1 = "BD"
Dim ColumnReference2
ColumnReference2 = "BE"
Worksheets("Sheet1").Range("H48").Formula = "='C:September[Book2.xls]Sheet1'!$" & ColumnReference1 & "$1 + 'C:September[Book3.xls]Sheet1'!$" & ColumnReference2 & "$1"
My problem with this method is that I need to automate the updating of ColumnReference1 = "BD" to ColumnReference1 = "BH" to ColumnReference1 = "BL" and so on and so forth. It's handy that the reference shifts across 5 columns, which remains constant. Ideally, I'd like to tell the macro to look at "AZ" then add 5 columns.
I'm trying to write a formula that will keep the Column ID static while allowing the Row ID to be reference. Each week my worksheet adds one line and the formula in question is moved one cell to the right and thus the formula cell makes the same move (1, -1). I need the formula to keep the column the same from week to week, while allowing the row to shift downward with the formula. I've attached a sample spreadsheet with the formula I derived. Check out the highlighted formula to familiarize yourself before running.
View 2 Replies View RelatedI am working on a sheet that has dynamic data saved. For any new value, a new row is inserted. I want to add a counter or more like an index so that I can see the number of the rows. I know that excel has is on the side but, I want to be able to make calculations. Like average of the numbers. Lets say I have 512 rows added. I want to be able (automatically via vba) to set the average with a sum of the values/ 512 which will be the number of the code I am asking. So I want a code that will start with the number 1 at (lets say) cell B2 and it will go like B3=2, B4=3.... any time a new row with data is added.
View 9 Replies View RelatedI am creating a training document for work; the sheet I'm creating is a summary sheet which works out how many people answered questions correctly, incorrectly (bringing in from another sheet), number of questions answered and percentage of correct answers.
Looks like:
Correct: =COUNTIF(sheet1!E2:E36,"correct")
Incorrect: =COUNTIF(sheet1!E2:E36,"incorrect")
Number of questions answered: =SUM(C4+C5) (correct+incorrect)
Percentage right: =SUM(C4/C6) (correct/number answered)
I need to drag these 4 formulas down into another 400+rows, however in the correct and incorrect formulas I need it to keep the 2:36 but change the e to f, g, h, etc.
I have the following formula. How can I change it so thst when copy/drag the column number automatically increments by 1
IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))
COPYING indirect formula. When I copy, the lookup reference does not change. My formula is =INDIRECT("'"&$A247&"'!j106"). When I copy horizontally across cells, I want J106 to increase, ie j107, j108 etc. At the moment it remains at J106 and i have to manually increase the numbers by 1.
How do I change my formula so that the numbers increase automatically?
In Column A, each cell up to A2197 has had data manually entered. A2194 has data AD453302085PIND, A2195 has data AD453302086PIND, A2196 has data AD453302087PIND.
Following on from A2197, I need to increment each cell by one number. I have modified the following solutions which were provided yesterday ="AD"&RIGHT(LEFT(A2196,5),3)+1&"PIND" and
=LEFT(A2196,2)&123+ROW(A2196)&RIGHT(A2196,3).
I'm trying to use the following loop to increment the column width of a range of columns thus:
Code:
NewWidth = 0
WidthIncrement = 0.5
For MyCount = 1 To 26
NewWidth = NewWidth + WidthIncrement
Cells(1, MyCount).EntireColumn.ColumnWidth = NewWidth
Next
If I set WidthIncrement to be an integer value, the code works correctly. If, however, I make it something like 0.1, all the column widths that the loop acts on are set to 0. I suspect it's something to do with how I'm declaring the NewWidth and WidthIncrement variables - they're currently set to Double. I know that I can have decimal values for column widths (e.g. 8.43)
In my spreadsheet, each row of data in Column B starts with 3 numerics (e.g. 001) followed by some text. For example:
Cell B2 contains '001 - Text for this cell'
Cell B3 contains '002 - Different Text for this cell',
Cell B4 contains '003 - Different Text for this cell', etc.
I have hundreds of rows which follow this format.
I have had to insert several rows in my spreadsheet in between existing rows which has thrown off the numbering in the first three positions of my Column B cells.
Is there any way to update the data in Column B so the text data in each cell is retained but the first 3 numeric values are updated and again sequential?
I have a formula that I want to insert using a macro, so how do I iterate the * values in this line?
ActiveCell.FormulaR1C1 = "='Sheet1'!R[*]C[*]"
Increment all cells located in Column H up by one (the one above it) cell Except for the header cell (since its the header)(H1),
H2 will always be a blank due to the make up of my database so that will not cause the header to be re-written.
Column H is not an entirely filled column and some cells are empty (if this info was needed in optimizing the code),
Im seeking the quickest and easiest way of implementing this since the database is very large.
Is it possible to use a contstant array in Excel VBA? I have tried several differnt ways to declare it, but the VBA editor keeps yelling at me. The following did not work:
View 2 Replies View Relatedi have problem with a long code.
So I divided that code in 4 parts.
So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()
I have declared
Option Explicit
Dim mdNextTime1 As Double
Dim myEnter As Integer
'Dim myK1 As Integer
'Dim myK2 As Integer
'Dim myK3 As Integer
Dim myK4 As Integer
Const myC As Integer = 82
Dim Lastrow As Integer
Dim myActualRow As Integer
all my sub have the following structure:
what does the symbol # means in VBA? (but I couldn't put the # in the subject of my message )
I'm trying to understand someone's code... at some point he wrote:
sum_LU_Area = 0#
I thought that the # was used to declare a constant but I'm not too sure because in his code earlier he declared
Public sum_LU_Area As Double
Beside, sum_LU_Area is calculated somewhere further in the program.
If I have a formula that I want to copy throughout a column, but I want to keep one of the parts of the formula constant, is there a short way to do this?
For example:
First cell is A1*O24
Second Cell is A2*O24
Third Cell is A3*O24
Right now when I try to copy the formulas, the second cell always reverts to A2*O25, which is not correct.
I try to adjust chart range using variable value instead of constant value.
My current code :
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Values = "='Check_2G'!$Z$2:$Z$32"
I need to make range ( "='Check_2G'!$Z$2:$Z$32" ) adjestable with varible like m instead of 32 in previous code.
For a single column of values, the following macro counts the first value twice, how can i fix this? for example: A1 thru A5 are a,a,b,c,d the result is
a 2
a 2
b 1
c 1
d 1
and should be
a 2
b 1
c 1
d 1
Macro:
Sub CountOfEachItem()
Dim ListRange As Range
Dim NewList As Range
[Code].....
Code:
Const Books1 = Array("5638", "5639", "10984")
Const Books2 = Array("3140", "7509", "3050", "7549", "7508", "7032", "6770", "6773", "4388", "6460")
I want to set an array as above for multiple use in my code (Used for auto-filtering various data with the same criteria).
The above is not allowed it seems, is there an alternative method so that if my Constant changes I only need to amend this once.
I've never had to venture into Arrays as there was no need.
I'm now faced with Arrays.
Generally I'm able to figure things out, in this case I'm stumped.
What I have is a Constant Array, that output the amount of Rows the Array displayed in.
I know the information is there, what I don't know is how to extend the rows.
ie it Outputs 5 Rows right now, with a top row header.
What I want is 10 rows with a header.
I want to basically set a varible that can be called back into a multiple of documents I will try and explain this as best as I can but it is a fortnightly pay date... i.e. 22/04/2009 Wednesday... as the payroll is fortnightly I want to be able to use the current date or todays date in a future proof macro.
i.e. if todays date is the 14/04/09 then the next pay date has to be the 22/04/09, if this was run in two weeks it would be 06/05/2009.
I was thinking a long the lines of lock in one start date... 14/01/09, then adding increments of 14 days until todays date - variable + 14 * r = "-"
I am just wondering if this can be possible:
this is an table with a lot of cells
12 36 89 56 97 78 98 36
36 89 56 97 78 98 36 12
97 78 98 36 56 97 78 98
12 36 89 56 97 etc ..
etc...
to become like this ...
I created a name in Excel 2002, Name = "NameToUse", Refers to = "Bill".
How do I get the value in VBA code?
Range("NameToUse").value ' doesnt work
NameToUse ' doesn't work
Option Explicit
Public Const ARK As String = "Inddata-ARK"
Public Const VVS As String = "Inddata-VVS"
Public Const KON As String = "Inddata-KON"
Public Const EL As String = "Inddata-EL"
Public Const LD As String = "Inddata-LD"
Public Const KØL As String = "Inddata-KØL"
Public Const OpDat As String = "Opdatering"
And then macro's under it... So the aboce is in module "INIT_S". Now in another module "RUN_P" I have a sub-rutine that uses these constants with a select case.
Likes this:
Sub OpEksInitNu()
Select Case ActiveSheet. Name
Case ARK
With OpEks
'more stuff
Case VVS
'more stuff
Case KON
'more stuff
End Sub
But when I try to run OpEksInitNu i comes with ambigious name with ARK as the error, later down I use another public constant. But i keep getting the ambigious name error.. What could cause that... I have tried to locate all ARK, VVS etc. to try to determine if it is used later on, but it's not! I only declare the constants one time, and no module is called ARK, VVS etc.
I'm looking to run several subs in a module which all refer to the same constant but I'm having difficulty defining that constant in a way that the various subs will accept. Basically I'm trying to define a constant LastRow. I am fairly new to VBA so am not sure whether to use a Get LastRow public property (which I tried to use and failed) or a Function and then how to make this readable by all the subs.
View 5 Replies View Related