Tagged Questions
1
vote
1answer
11 views
VBA to merge cells in excel
I have a excel sheet like below. Consider the sno column as Excel numbering.
So all the columns except DateTime are merged. I want to Merge the DateTime cells and want to delete the empty records. I ...
0
votes
1answer
16 views
VBA Code not recognizing dates in certain format
I am working on a code that uses a unique ID number and associate date to see if a same record already exists in the worksheet. Here is my code:
Part of first code:
Else
'If all the data has ...
-1
votes
0answers
9 views
MS Excel Web browser object [on hold]
I have installed browser object in MS-Excel (2007) using developer tool (webbrowser object) without any problem but my requirements are
a) on click of the link like http://google.com link should ...
0
votes
1answer
29 views
Hidden data in excel
I would like to know how can we get the hidden data about the excel workbook programmatically e.g. which cell value is hidden, hidden row, hidden column, hidden sheet etc etc? how this data is stored ...
1
vote
3answers
36 views
How do I add “1” to all cells in a range using Excel VBA?
OK, I'm trying to add a unit of "1" to many cells in a range. Below is where I'm at and I keep getting a type mismatch error. I'm not even sure if this is the right place to start in the VBA, but I ...
1
vote
1answer
19 views
For without Next Error on Email Macro
Hello I have this code here returning an error "For without Next" and it highlights the end sub all the way at the bottom.
Why is this happening? I'm just trying to insert the value of certain cells ...
0
votes
0answers
12 views
Finding values in excel
Hey everyone what I'm trying to do is to get a highlighted search in excel that will also take you to the cell highlighted. I already can make it highlight the cell I'm looking for, also without using ...
0
votes
2answers
35 views
How to remove null characters in excel 2007?
I have a data that has blank cells. However, this are not actually blank. If i used teh =isblank() function, it returns false.
I got this code from a website
Sub NoNull()
Dim c As Range
For Each c ...
1
vote
3answers
38 views
Excel - Split apart a list
I have a list of about 300 items, that I need spaced out every 8 cells as opposed to being one after the other. I'm sure there is an easy way to do this, however my brain is failing me. I have a ...
0
votes
2answers
24 views
Loop through cells to send email
Hello I have a macro that is supposed to send emails to the addresses on my C Column with copy to the email addresses in the F column and some text... But my problem is that it is not looping through ...
0
votes
1answer
38 views
VBA Excel to PPT export
I'm trying to transfer some code from one workbook to another and I'm having trouble figuring out why it's not working. I transferred the sheets into the new workbook and made the necessary updates in ...
0
votes
0answers
57 views
Excel Macro VBA Graphing and Saving Dynamically
Good day.
Heres my situation. I have excel running a macro once a day at 5pm that imports data from a txt file, graphs it,
saves it and closes it. I need help with the graphing and saving part. ...
0
votes
2answers
48 views
Excel VBA Macro Graphing Dynamically
I have an excel file that opens automatically then it runs a macro. This macro imports data from a txt file. Every 1 hour, data goes into the txt file into a new row (time stamp and a value). My macro ...
0
votes
0answers
40 views
Creating “Reset” Button in VBA
I have inserted a shape, (Rectangle) and I am trying to use it as a "Reset" button to change the selection in 2 Data Validation cells, F17 and G17. I assigned a macro to the shape with the following ...
0
votes
1answer
21 views
Increase length and spacing of underline in pie chart title
I have a title in a pie chart in Excel 2007 whose text is underlined.
Can I make the line space between text and the underline larger and make the underline longer than the text itself?
I tried ...
0
votes
1answer
19 views
insert a chart object at a specific location
I have written a macro in excel to insert a chart.
The macro is run when a particular range of cells is selected.
It is:
Sub drawchart2()
'
' drawchart2 Macro
'
'
Range("B24:C36").Select
...
0
votes
1answer
14 views
Excel numers automatically formatted with Php currency
I am using a macro to do my daily tasks on excel. Recently I bought a new laptop with excel 2007 in it and I noticed all numbers in a cell are automatically having a Php currency in them. I can ...
-3
votes
0answers
87 views
Split excel document by # of rows into multiple files and convert to csv
The below script was exactly what I am looking for to split an excel document into multiple files by a number of rows. I am not usre how to change it to save the file as a csv file. It saves as a ...
1
vote
1answer
26 views
How to improve refreshing Pivot data speed
I have this code which refreshes pivot table data.
Dim wsPT as Worksheet
Dim pt as PivotTable
Set wsPT = Thisworkbook.Sheets("Pivot")
With wsPT
For Each pt in .PivotTables
...
1
vote
1answer
42 views
Ribbon Invalidate doesn't work in Excel 2007 when loaded as a VBA .xlam
This is a weird head scratching one!
I have a cross-MSO add-in written in VBA that uses the same XML definition, ribbon controls and VBA callback procedures. It's working for PowerPoint 2007, 2010 ...
0
votes
0answers
36 views
How to improve this code?
Here's my current code.
It copies Sheet1, Sheet2 and Sheet3 to a new WB then copy and paste values.
How do I do this without using Select?
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
...
1
vote
3answers
34 views
Combine two subs with changing ranges
I want to combine these two lines of code but cannot figure out how to get it to work. They both work separately, but I want the first line of code to be the first operation and then the second sub ...
1
vote
2answers
32 views
Prompting a Msg Box when data in an Excel cell range updates
I have searched high an low for this but cannot get it to work for my entire range. And this is my first post, so be gentle.
I have an algorithm that analyzes the time that certain products should ...
0
votes
2answers
63 views
How to better update pivot table data filter?
I'm using this code below to update pivot table filter.
It only filters items under date field equal to todays date.
It is workning fine but is there a faster way to do this?
Dim wsPivot1 as ...
2
votes
2answers
43 views
How to avoid Runtime Error on Range Selection from WS already declared and set
I have this code and it is working fine if i run the macro while Sheet1 is selected or activated.
however it fails when other sheets are selected. Why?
I get a Runtime Error: Select method of range ...
1
vote
1answer
47 views
How to properly code this in excel vba?
I have this code and it gives me a Compile error: Expected Function or Variable.
How do i code this properly.
Sub test()
Dim wb As Workbook
Dim FiletoOpen
FiletoOpen = ...
0
votes
2answers
99 views
Change plus to minus based on another cell value
please can one of you help me with the following code. i got somewhere on the internet and wanted to change it, but not winning,
i want the amounts in column I (from row 9) to change to a minus ...
0
votes
1answer
97 views
find and edit data using a form in VBA
UPDATED - I have the form as shown below. The form can populate the list in the combobox. It can also locate the notes attached to the appropriate username. However, i need it to update the same cell ...
0
votes
1answer
22 views
How can get colors in Axis Labels
I had problems with pivot chart. Apply data labels to my chart, I select the data labels and open “format data labels” windows, go to numbers tab. Select “custom” and code like this : [black] +0%; ...
0
votes
0answers
128 views
Select highlighted merged cell, copy it and paste in other sheet
In the Excel sheet below, the field containing india is a merged cell. How do I select the merged cell with a particular color and paste it into a single cell in another sheet?
Below is the screen ...
0
votes
1answer
103 views
Insert or Delete column if does not equal heading
I have column headings starting in Column L going forward. The column headings need to be in a certain order.
By hand, I have to insert columns so everything is in order. I put insert or delete in ...
0
votes
1answer
66 views
How do I change the file print setting on Excel to use VBA to print several pages when printing the workbook
I would like to change the file print setting in Excel to go directly to VBA and print some of the 26 worksheets while printing the workbook. Most of the active worksheets should print, but a few of ...
0
votes
2answers
91 views
Excel 2007 macro fills to end of first sheet for all sheets in workbook
The code below creates a new column (A), gives the it the header "Class" and then fills column A with the worksheet name until the last row of B for all worksheets in workbook. It is working except ...
1
vote
1answer
135 views
How to determine if a cell formula contains Constants?
When debugging or Quality Checking an Excel report at work I have found that the problem was because of text being hard coded inside a formula. I have heard this as being a Constant and Formula mixed ...
1
vote
1answer
99 views
Run-time Error '13' Type Mismatch IF value = Then Statement loop
Basically what I'm doing is I have data in columns A-I and I'm trying to copy out data [Columns D-G] by their value in D. In this loop I'm looking for when the entry in D says "1CME Cash-Settled ...
0
votes
0answers
135 views
Combine Duplicate rows from different worksheets in Excel
Would like to enhance the following code to do the following:
Look for duplicates rows throughout the worksheets
If there are duplicates, ensure that all the possible values combine are listed ...
0
votes
0answers
89 views
how to get the primary x-axis value which is corresponding to the secondary axis?
I have created a Chart of 3 Labels-Hours, Marks and Score. Hours and Marks are in primary axis(Column Chart) and Score is in secondary axis(Line Chart).
Now, how to get the primary x-axis(Marks) value ...
0
votes
1answer
43 views
Move entire contents of cell based on whether it contains a specific character-Excel 2007
I want to create a macro in Excel 2007 to search Column A for a colon (:) and if found, move the entire cell value into the same row Column B. Thus it my data looks like this:
Column A Column B
...
0
votes
1answer
60 views
VBA for determining chart type by number of rows in table
I'm trying to write some VBA that will automatically produce a certain type of chart based on the number of rows in a table. I'm using an IF loop, as below, based on a lastrow variable. i have ...
0
votes
1answer
58 views
How to get a value of a cell knowing it's unique ID, stored aside?
I have in my Excel spreadsheet a row which contains product names and a parallel row which indicates their prices (each one has a different price). I need to get the name of the most expensive ...
0
votes
3answers
89 views
how to find the max 'n' values for data based on SUM of values?
Below is the table
Amt | Val | Location
230 | a | DEL
450 | b | KOL
670 | c | BLR
890 | d | DEL
111 | e | KOL
133 | a | KOL
155 | b | DEL
177 | ...
1
vote
0answers
63 views
How to run & open Excel file from server?
I have an Excel file installed with Add-Ins. If I share the excel sheet with other person, the sheet requires the same add-in to work.
Is it possible to place my excel in common repository and users ...
0
votes
1answer
239 views
How do I write a loop in VBA that will delete a row if a cell in a range and the cell next to it both equal a constant?
[This is in Excel 2007]
In other words, the loop will cycle through all the active cells in a one-column range (rngAddressName) and, if the cell in the range AND the cell directly to the left of it ...
0
votes
1answer
151 views
Excel Macro to hide rows across multiple sheets
I have a macro that hides rows based on cell values. I am attempting to use this across multiple sheets in the workbook, but not all of them. My code below seems to run the macro multiple times in the ...
2
votes
1answer
369 views
Merging cells in Excel without loosing data
First of sorry for making a new thread about this but i wasn't able to comment in existing threads.
I'm trying to merge a lot of cells exactly like in this thread, but I'm kind of new to coding and ...
0
votes
1answer
597 views
Changing a PivotTable filter by entering a value into a cell
Have looked around for an answer and infact the code that I am using is from this site. I have a VBA for changing PivotTable filters by inputing a value into a seperate cell like so -
Sub ...
1
vote
1answer
122 views
Creating an Archive Macro Excel VBA
In this latest project the desire is to have a button & macro that will do the following:
When clicked the macro will copy all the data from the existing workbook & save it to another ...
0
votes
2answers
71 views
Re-setting all option buttons at once
I have written a code for re-setting all option button on one click but its giving an error, "object doesnt support the property or matter".
Sub Add_New_Record()
Dim i As Integer
For i = 1 To 30
...
-1
votes
1answer
149 views
VBA Copy & paste chart in excel
Im trying to copy a graph from on sheet when a condition is met. For example
IF range ("G5") = "Team 1" Then
Copy and Paste Graph at range (H15:N31)
Then remove the graph when the work ...
2
votes
2answers
48 views
Excel VBA IN keyword
Is there a better way to code this in VBA:
If x = 'a' or x = 'b' or x ='c' or x = 'd' then
...action
End if
The IN keyword would come very handy here:
If x IN ('a','b','c','d') then
action
End If
...
