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.
I need to create a file with random purchase list that have some restrictions.
I’ve been working on this file for few days, but I couldn’t get desirable results.
Attached a sample file that describes what I did. It build up as the final file (just the final file has many more items).
I’ll try to explain what I need and how it works:
There are 4 categories: Theater, Salon, Kitchen & garden.
I need to create random lists of purchases (each list should be a line at the total sheet)
there is budget limitation of $100000. Each category has minimum items that must be purchase (headlines are in red color, total of 9 items).
The minimum items that can be on one list are 11 (9 red columns & another 2 random columns). Maximum are 15 items (9 + 6). Same item shouldn’t appear twice at the same list.
This file chooses randomly 19 items and check if the list stands up the budget.
There are 3 issues that I couldn’t solve by myself:
1.I should define the file to stay within minimum & maximum items per list (11-15)
2.Make sure it doesn’t go over the budget
3.Stabilize the list the values will not change each time I’m doing changes to file
I am trying to create a VBA code that will search through a column"A" and if it finds a calue that C1 then color it, when it finishes searching columnA then loop again and search for items that match C2
Here's what I have so far with no success
Sub ColorItems()
Dim i As Integer
Dim j As Integer
Range("C1").Select
For i = 1 To Selection.CurrentRegion.Rows.Count
For j = 1 To ActiveCell.Offset(0, -2).CurrentRegion.Rows.Count
If ActiveCell.Offset(0, -1) = ActiveCell.Value Then
With ActiveCell.Offset(0, -2).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
I would like to creat a check box for a cell. just a simple box where when you click on it it makes a check mark
View 9 Replies View RelatedI 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".
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 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.
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 RelatedHow 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
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
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 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.
I want to freeze rows 1-18 and columns A-P. But when I select the the cells and choose freeze panes but what it freezes is Rows 1-13 and columns A-H.
View 1 Replies View RelatedI am using Excel 2010, I receivean error message "end if without block if" I've been working on this forseveral days and I have been un-successful. The code is to look at what is in combox1 and based off what is selected by the user, it will locate that tab and put in the information from the userform.
Private SubCommandButton1_Click()
"S:location of file.xlsx"
'UPDATES WORKBOOK
[Code]......
Public Sub PasteData() Dim k As Integer Dim i As Integer Dim ro As Integer Dim co As Integer
ro = 3 co = 21
With boxi Range(.Cells(2, 1), .Cells(calls + 1, no_material)).Copy End With With aus For k = 0 To (no_material - 1) .Cells((3 + (k * calls)), 1).Paste Next End With With FIinfo For i = 1 To 20 If .Cells(i, 2).Interior.color = 5287936 Then .Cells(i, 2).Copy With aus Range(.Cells(ro, co), .Cells(ro + calls, co)).PasteSpecial ro = ro + calls + 1 End With End If Next End WithEnd Sub
The line in italics is the one causing the problem, I keep getting "Object variable or With block not set" error.
aus and boxi are both publicly defined in the module to be worksheet.
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 ...