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 want to do is copy data to my working sheet (say sheet-1) from other worksheetx (say sheet-2, sheet-3). That's easy enough, but I want to be able to indirectly address "sheet-2" or "sheet-3" from a cell in sheet-1.
Look at the attachment. The data under cost A, cost B, cost C is from other sheets in the same workbook. I want to able to type in "sheet-2" in the first column and Excel to automatically copy over the data in columns 2,3,4.
I do not want a VBA solution. I know this can be done with built-in Excel functions because I did it before. Unfortunately, I lost that spreadsheet and I can't recall how it was done. I tried using Indirect function, but it returns a ref# error.
how does one alter a variable without referring to it directly? Eg. One has a combo box (CboPNum) with values from 1 to 20 and textboxes (TxtN1 to TxtN20). Upon selecting a value from the CboPNum dropdown, the background of the corresponding TextBox should be changed. I’ve tried to create something that joins the string “TxtN” and the CboPNum value:
ThisTextBox = “TxtN” & CboPNum.Value ThisTextBox.Background = Black
But this quite correctly gives the error “Object required”… What do you guys recommend?
I need to ask user to input the row ("rowin") and column ("colin") address in order to activate a cell. The mose natural inputs from the user will be an integer for row and one or two alphbats for column. Say, for example, the user input (82, AY), how do I represent the cell in code? I know I want to say something like Range("AY82"). But I just could not figure out how to concatenate "colin" & "rowin" to make it AY82 to Excel.
I'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".
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.
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.
I'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
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 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
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 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 ?
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
It 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
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 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 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.
