I have a database with dates in coloum F, coloums G to Q is where different product tonnages are entered. Cell Report C2 is the start date and cell C3 is the end date. I can calculate the total tonnage of a product (coloum J) bu using the formula
= Sum(Data!J:J)-Sumif(Data!F:F,""&Report!C3,Data!J:J)
This works but now if I want to total from coloum G through to coloum Q between the dates I cannot get it to work.
How do you block out the past dates?
Like today is september 14th, tomorrow I want september 15th to be highlighted but all dates previous to september 15th to be black.
Here is the code if it helps any one out. Thanks in advance for the help.
Sub CreateCalendar()
Dim lMonth As Long
Dim strMonth As String
Dim rStart As Range
Dim strAddress As String
Dim rCell As Range
Dim lDays As Long
Dim dDate As Date
'Add new sheet and format
Worksheets.Add
ActiveWindow.DisplayGridlines = False
With Cells
.ColumnWidth = 6#
.Font.Size = 8
End With
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
End Sub
I am looking out for a code where I can copy a block of data to the second sheet and adding the new block of data below the previous one.
The block of data ranges from E6 to K15. On a click of the command button the data gets copied to sheet2 starting from A2. It will be pasted in sheet2 from A2 to G11. When I click the command button with the new data it will be added from A12 to G21 after that A22 to G31 and so on It will keep on adding the block.
I have a matrix with info of persons of different cities in consecutive rows, where the
city data is in column B. I would like to eliminate columnb B and divide all persons from
any particular city with 2 new rows, their city name and below a field for every column data.
*The number of employees for city would be variable.
Original data (The columns used go from A to E)
1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1
2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2
3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3
4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4
5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1
6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...
I have a quite big database as a result of a study in Excel 2010. There is a special block of data for every participant in the study, including 16 data series in 16 lines. I have to make a unique chart for every line.
One block is bulid up in a way, that every row is the input for a chart, with the exception of one "spacer" row, which is empty.
Name
1 2 3 4 ...
A
B
C
D
E
F
For example in the sample above, there should be 6 line-charts (A, B, C, D, E, F) next to the block.
Can a macro be written, so I can automatically create the chart-set by selecting the block's upper left cell and running the macro? Or is there any easier method?
I need to use Excel 2007 in a stock sheet situation.
I have attached a picture of what the sheet will look like.
What I need is a formula that will take a value in column c and then add the value in colum a behind that. Preferably with a X or - between them ie.
If i took the first row and applied the formula it should kick out 5 x Jam
The main thing is it must output the answer to another file/page so that the main page stays the same. And if there is no value in Colum c it must ignore it.
Thats the best I could describe that What I face today is the means to run a VBA Autofilter from an Activecell. The Activecell is a search result. I have a table that spans from A1 to E5000. The Activecell will always be in Column A. What I need to happen in when the Activecell is found an Autofilter is placed in the block of data from Column B to E. The rows will change after every search hence my dilemma. Each block of data has its own header as well
So if my Activecell is A2 then I need B1:E4 Autofiltered then Column E sorted Smallest value to Largest. The Activecell needs to remain static. The size of the blocks of data are exactly the same.
Here is a photo example of the desired result. Untitled.jpg
There are hundreds of blocks of data like this.
I am trying to select a block of data from a remote worksheet but I keep on getting the following error
Run time error '1004':
Application-defined or object-defined error.
I have attached the file and I am working on worksheet 21 "PT Monthly Report 1"
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I have made this simple code which replaces an empty cell with "Empty" on a button.
View 4 Replies View RelatedI'm using combobox to display set of data. When you choose value from ComboBox (lets say "Audi"), below on the sheet you will get all information about audi in rows:
year....
color....
dors... etc
I know that somehow the ComboBox can be unfortunatly deleted or moved etc. What else I can use to replace ComboBox?
It's option "Data Validation", but I don't know how to there assing macro. I can create button and assing to him, but its other way?
I have been out or writing code for a long time and my experience prior to that is very limited.
Rows "H" and "I" may contain the same words, "H" will be only one word, "I" will contain a string of words w/ the first word matching the word in "H", eg.
H6 = "Alien" (w/out "")
I6 = "Alien W. A.V.E (w/out "")
I would like to put a block of code that evaluates H6 to I6 and if they match then delete the first word and the space in I6.
This would be done over the range of H6:I and the last cell populated in "I".
I'm new to VBA and I'm trying to creat an If block.
If "named range" is TRUE then....
Could someone quickly tell me the syntax for this.
The range is actually on a different sheet than I am working on, but the named range is global.
In my case my named range is "WBSCheck" and it is a logical/text value of True or False.
This supposed to block Alt key and all combinations with it except Alt+F1, Alt+Tab and Alt+Shift. But what I get is: if I press Alt+F1 only, F1 is triggered.
Private Function LowLevelKeyboardProc(ByVal nCode As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
Static kbdllhs As KBDLLHOOKSTRUCT
If nCode = HC_ACTION Then
Call CopyMemory(kbdllhs, ByVal lParam, Len(kbdllhs))
If CBool(kbdllhs.flags And LLKHF_ALTDOWN) Then
Select Case kbdllhs.vkCode
Case vbKeyF1, VK_LSHIFT, VK_TAB
Case Else
LowLevelKeyboardProc = 1
Exit Function
End Select
End If
End If
LowLevelKeyboardProc = CallNextHookEx(m_hDllKbdHook, nCode, wParam, lParam)
End Function
I have a column of numbers of approx 3000 rows and the number is either a 0 or 1 and they are in a random order in each row. I would like to count the number of 0 in the column. If there is consecutive 0 (a block of 0), I would still need to count it as a single occurrence.
For example, the count (or sum) for the number of occurrences for the number 0 in the below would be 4.
0
1
1
0
I need is for a block of cells (A1:I4) to be visible at all times. I've frozen the sheet so that the top four rows are always visible, but unfortunately the sheet is quite large, horizontally-speaking, so that when users scroll horizontally, important stuff scrolls off the screen.
View 9 Replies View RelatedI'm struggling to find the right Excel functions to fill up a block matrix. Here are all the details of my problem.
I have a list of firms and relative products. Each firm can produce either 1, or 2 or 3 different products, for instance
firm A produces product x
firm B produces products y and z
firm C produces product j
firm D produces products k, h and q
Suppose the data appear on a sheet in column order, that is, one column for the firms and one for the products with a space between A, B, C and D when one firm is a multi-product producer. I need to create a 0-1 block matrix in Excel setting 1 when the product is produced by the same firm and 0 otherwise, i.e. for the above example
x y z j k h q
------------
1 0 0 0 0 0 0
0 1 1 0 0 0 0
0 1 1 0 0 0 0
0 0 0 1 0 0 0
0 0 0 0 1 1 1
0 0 0 0 1 1 1
0 0 0 0 1 1 1
I can do this in VBA but not in Excel, and unfortunately I need to provide an Excel solution.
I have a sheet with data and in column A there is the month. Now each month the macro adds new data for the new month.
What I want now is that i can load the same month twice. But of course when I do that first the old month has to be deleted from the rows. I already build a check that first looks if the month is already in.
So when it is already in it should find first row and last row with that month and then delete al these. Then when it is deleted the macro can continiou with pasting in the new month data.
But letting it check row by row and deleting row by row is no option! Its a long list and I aready tried that, it is just verry slow that way.
ps: all the month data is grouped as month. So no other month lines are in that block of data.
I have a named block, used to sort a list of data. But I names the block one row higher to include the headers, so that inserts at the top row of the data would not fall out of the named range. Then I want to sort the data, but not include the header row.
What I want to say in the sort macro is something like:
cell_range( top_row(Data)-1, Bottom_Row(Data) )
Or, to have two names, with one defined in terms of the other:
Data_sort = Data_big - Toprow(Data_big)
I am getting the error message
"Compile Error: End If without block If" for the code below :
ErrorHandlerRoutine:
If MyInteger = 1 Then
MsgBox "Data does not exist in the CO2 data set"
End If
Resume 1
If MyInteger = 2 Then
MsgBox "Data does not exist in the Water data set"
End If
Resume 2
If MyInteger = 3 Then GoTo 3
MsgBox "Data does not exist in the Waste data set"
End If
Resume 3
I have another question. When address come through our DB into Excel, they are in one big block. In other words the city and state and zip are in a lump with the street address. Text to columns does not work because it does not break up the text consistently. What can I do?
I'm trying to find cells in a range that have a date >= today.... this wont work:
Set Cell = .Find(What:=Cell.Value >= Date, After:=.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
Can it be adapted at all?
I have a log that information gets enter into by rows. When one rows info is entered a "Start" button is to be clicked and a macro runs and does different calculations. I want a way to not let the next rows information to be entered until the "Start" button is clicked on or Is there a way to have the code run automatically when the user has entered the info in the last cell of the row and goes back to the first cell in the next row ?
View 3 Replies View RelatedIt seems like this error applies when it can't find something you're referring to, like an object variable. But I can manually find every variable in my code and can't see what I'm missing:
Public Sub InventoryFile()
Public InventoryWksht As Worksheet
Public CriteriaWksht As Worksheet
Public Criteria As Range
Public InventoryRange As Range
Public Sheeti As Worksheet
With Workbooks.Open("MyFile.xls")
Set InventoryWkbk = ActiveWorkbook
Set InventoryWksht = InventoryWkbk.Sheets(1)
End With
With InventoryWksht.Cells(1, 1). CurrentRegion.Name = "Inventory"
Set InventoryRange = InventoryWksht.Range("Inventory")
End With
Worksheets.Add
ActiveSheet.Name = "Criteria"
With CriteriaWksht
Set CriteriaWksht = ActiveSheet
CriteriaWksht.Range("A1:B2").Name = "Criteria"
Set Criteria = CriteriaWksht.Range("Criteria")
Criteria.Cells(1, 1).Value = "Value"
Criteria.Cells(1, 2).Value = "Location"
End With
End Sub ...
I'm trying to set up a water bill calculator and am having troubles. We have a base rate that we charge for everything under 4000 gals. After that we have an increasing tiered rate. I'm trying to make a form so we can calculate bills accordingly. I don't know how to create an "IF" scenario.
example
If "total usage" is greater than 4001 but less that 8000 multiply by $4.50 per 1000 gal
If "total usage" is greater than 8001 but less that 12000 multiply by $4.75 per 1000 gal
If "total usage" is greater than 12001 but less that 16000 multiply by $5.25 per 1000 gal
If "total usage" is greater than 16001 but less that 20000 multiply by $5.50 per 1000 gal
If "total usage" is greater than 20001 and over multiply by $5.75 per 1000 gal
Is there an easy way to do this?
developing a formula that will apply data from one cell to a block of text in another.
Here is what I need to do.
1. I want to insert the data in A1 of the attachment to the block of text in B1.
I can do this manually but I have to do this 10,000 times so I need a formula that I can apply.
task of inserting a block of cells into a worksheet.
I have saved 4 different blocks of cells and depending on an option chosen from a drop down menu i want to select one of more of these sets of data and paste them into a worksheet.
I want to convert a bloack of rows to columns
1abc2xyz3mnq
4efg5klm6rst
required format
1abc
4efg
2xyz
5klm
3mnq
6rst
I have an issue with a user form I have created. When users open the form there are certain options (ie location) which are a combo box and I only want users to be able to select one of the drop down options.
Sadly they can also type in the combo box. How do I block users from typing in a combo box in a user form so that they are forced into selecting one of the drop down options.