Here's a link too
http://excelhints.com/2008/10/21/protect-cell-formats/
Regards,
Sam
On Oct 21, 3:06 pm, GoldenLance wrote:
> Please protect the sheet, and use 'Allow Users To Edit Ranges' option.
> Your format will remain, and users will not be able to edit, but you
Please protect the sheet, and use 'Allow Users To Edit Ranges' option.
Your format will remain, and users will not be able to edit, but you
will be able to add data.
Regards,
Sam Mathai Chacko (GL)
On Oct 21, 10:45 am, Ayush Jain wrote:
> Hello members,
>
> Thanks for your great participation i
Try this
http://www.excelfox.com/forum/showthread.php?66-Execute-SQL-From-Excel-(VBA)
Regards,
Sam Mathai Chacko (GL)
On Oct 21, 12:43 pm, airen wrote:
> Hi,
> I want to update a table in SQL server from an excel file. Please
> advise a VBA code.
> Thanks
> Akhilesh Airen
--
You might want to clarify 'I need the formula for the GREEN cells to
lookup the value in the appropriate table', with an example.
On Oct 7, 5:44 pm, "John A. Smith" wrote:
> I need the formula to lookup a value in a table and return the table column
> header value. Please see attached spread she
To unprotect sheet / workbook structure protection, this should help.
however, this will not open the workbook, if you have protected the
file.
http://www.straxx.com/excel/password.xla
Regards,
Sam
On Oct 7, 12:41 pm, anil kumar wrote:
> Hello everybody,
>
> I have a excel file in which i use
This short and simple macro should reverse the range for you
Sub RangeReverser()
Dim rngCell As Range
Dim rngCells As Range
Dim lngCounter As Long
Const lngOffsetRow As Long = 6 'How many rows below you want the
reversed range
Set rngCells = Range("A2:M2")
For Each rngCel
Ensure this code is in the same parent sheet code module
On Sep 24, 6:49 pm, Markkim wrote:
> Hi
>
> The following code is a testing code before creating more codes..
>
> when I run the code below, I'm getting an error message "Object
> required".. then Comboschooltype.Text is highlighted..
>
> I
Ahmed, use this in F5 in your original file, and drag down
=SUMPRODUCT(MAX((A5:A1032>=VALUE(D5))*(A5:A1032 wrote:
> I deleted your original but here is a sample
>
> From: Ahmed Honest
> Sent: Sunday, September 25, 2011 9:20 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Max
Here's a VBA method.. this goes to the sheet module of course...
Regards,
Sam Mathai Chacko
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, ActiveSheet.Cells.Find("Top
Poster").Resize(11, 2)) Is Nothing Then
Application.EnableEvents = 0
No, it is not. Didn't think of it much to be considered offendable.
But got your point. Won't refer to you in that form, Sir. I also
realize that one should code 'efficiently'.
In the interest of the forum decorum, I would like to take
responsibility for the exchange of words that happened here wh
Sub OpenFile()
Dim File As String
File = InputBox("Vnesi nalog")
If Len(Dir("C:\Users\user\Desktop\2011_" & File & ".xlsx")) <> 0
Then
Workbooks.Open Filename:="C:\Users\user\Desktop\2011_" & File
& ".xlsx"
Else
MsgBox "File does not exists!"
End If
End Sub
R
You cannot reference a variant array using a byte array even if the
data contained within the variant array is all byte.
Suggest using Sub ConsumesByteArray(ByRef ByteArray)
On Sep 7, 3:26 pm, ShayneH wrote:
> Is there a way to expose an array that is enclosed by a variant?
> Basically I have on
TextBox2.Text = Application.RoundUp(Val(TextBox1.Text), 0)
Regards,
Sam Mathai Chacko (GL)
On Sep 7, 6:52 pm, Shankar Bheema wrote:
> I am attaching an excel file with inserted userform.
>
> I have given command button to roundoff the figure in textbox1.text to the
> next higher rupee. I have
Chris, doesn't look like anything is wrong with your settings. If A3
wouldn't be a problem, try changing the paper size to A3, from A4
Sam
On Sep 5, 10:24 pm, "Chris Boxall" wrote:
> Apparent corruption with .PageSetup.Zoom
>
> I fear that I have corrupted my installation of Excel 2007 (on XP).
>
Rajan, you make us proud. Thanks for the excellent contribution to
this forum. Your dedication and commitment to help the online
community has been exemplary. Keep up the good work.
Regards,
Sam Mathai Chacko (GL)
On Sep 5, 10:29 pm, Ayush Jain wrote:
> Hello Everyone,
> Rajan Verma has been sel
=SUMPRODUCT(LEN(A1)-
LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))
from
http://www.excelfox.com/forum/showthread.php?135-Count-Alpha-In-AlphaNumeric-Text
GL
On Sep 5, 9:09 pm, Intelligent But Crazy wrote:
> Thanks Noorain,
>
> for your Quick Help. but actually my requirement is F
Try this
http://www.excelfox.com/forum/showthread.php?125-FreezePane-In-A-Userform-SpreadSheet-Control-11.0
Regards,
Sam Mathai Chacko
On Sep 1, 2:29 pm, John Holland wrote:
> Would there be possibility to freeze panes in an Excelsheet Control
> on a userform? I can find certain VBA-code t
lightly difference issue with YTD results.
>
> >> Is there someway to modify the formula in cell D9 (modified
> >> formula to be used in cell N9) so that it would return the sum of
> >> the months beginning with November thru the month shown in cell J3?
>
> >> Thank you for your help!
>
> >> Steve
>
>
Application.OnTime is designed for such uses. The Application.Wait
halts all code, where as OnTime allows the sequential flow of the
macro.
Code below
Sub Start()
Range("G3").Value = Now
If Range("G3").Value = Range("E3").Value Then
MsgBox "Time's Up", vbInformation
Else
Use =INDIRECT("'2011
Forecast'!"&ADDRESS(28,MATCH(TEXT(J3,""),'2011 Forecast'!$A$2:$X
$2,0))) in Monthly IS, range F9
Steve, sure you can replicate this for all your cells.
Regards,
Sam Mathai Chacko (GL)
On Aug 19, 9:53 pm, Steve Weaver wrote:
> Hi,
>
> Can some one help me to create a fo
Nice one XLS S
On Jul 31, 10:41 am, XLS S wrote:
> Dear All,
>
> Try this
>
> 1. In a blank cell, enter the number 1.
> 2. Select the cell and press *Ctrl+C*.
> 3. Hold down the *Ctrl* key as you click each hyperlink you want to
> remove.
> 4. Right Click and Choose Paste Special f
=INDEX($A$1:$AE$1,SUMPRODUCT(MAX(($B2:$AE2="P")*COLUMN($B$1:$AE$1
will work without an array formula Dan :)
On Jul 31, 5:20 pm, "Daniel" wrote:
> Hi,
>
> In AF2 (array formula, validate with Ctrl+Shift+Enter) :
>
> =INDEX($A$1:$AE$1,1,MAX(IF($B$2:$AE$2="P",COLUMN($B$2:$AE$2
>
> Copy down.
XLS S, I would tend to believe that progressing from small to large
indices for a row will not solve the problem.
A more conservative approach would be the classic reverse look that
looks like this
Sub DelRowWithZeroes()
Dim lngLoop As Long
With Worksheets("Sheet1").Range("P3:P13")
You might be looking for Excel dashboards then. Try chandoo.org, they
have some nice dashboards..
On Jul 25, 1:43 am, Adam Hamilton wrote:
> Hello, I need to take employee data, and run a macro that will allow
> me to populate many different types of conclusions from this data.
> For example,
>
Try using a Pivot Table. one of the quickest and easiest ways for
data consolidation and view in Excel
Sam Mathai Chacko
On Jul 25, 1:43 am, Adam Hamilton wrote:
> Hello, I need to take employee data, and run a macro that will allow
> me to populate many different types of conclusions from
menu. But it is not appearing in my PC. I think some
> of the reference has not been added.
>
> So which reference should I add to get the additional controls?
>
> Regards,
> Alisha
>
>
>
>
>
>
>
> On Sun, Jul 24, 2011 at 7:52 PM, GoldenLance wrote:
>
=IF(B1 wrote:
> Thank you so much sir, but I have one query, the maximum salary must reflect
> against relative salary, becuase there is further formula for other members
> to increase rest of the members their salary by 20% of maximum.
> could you please help me out further please find attched sam
Dear Group Members,
We are looking for an Excel Specialist, one who is well versed in
Excel formula and it's application in various situations (should be
well versed with text functions, lookup functions, and array
formulas), at least intermediate level VBA skills (able to develop
generic independ
Taking Ashish's example which showcases the more versatile sumproduct
function,
Use =IF(COUNTIF($A$2:A2,A2)=1,SUMPRODUCT(MAX(($A$2:$A$15=A2)*($C$2:$C
$15))),"") to display the maximum salary only once against each family
Sam Mathai Chacko
On Jul 24, 4:55 pm, vickey wrote:
> How can I get maximu
Vikas, if your data start from A2 (excluding the heading, use the
large function in an array formula
=IF(COUNTIF($A$2:A2,A2)=1,LARGE(($A$2:$A$15=A2)*($C$2:$C$15),1),"")
Use CTRL+SHIFT+ENTER to confirm an array formula
On Jul 24, 4:55 pm, vickey wrote:
> How can I get maximum value from a set of
Use Date and Time Picker Control 6.0 (SP6) from the tool box. That
would list the control in the toolbox window. Click on it, and use it
on your userform.
On Jul 24, 6:13 pm, alisha malhotra
wrote:
> Hi,
>
> I am using one userform, In that I need Date & time Picker in excel 2003.
> But when I cl
A Pivot Table is used precisely for such computations. As a thumb
rule, use Excel functions and tools where Excel can handle it
efficiently. Use VBA only if Excel cannot give desired output on its
own
On Jul 21, 10:19 pm, Raghavendra wrote:
> Hi,
>
> Can anyone let me know VBA code for the below?
@Anish, that's cool dude, just trying to help.
Sam Mathai Chacko
On Jul 21, 9:41 pm, Anish Shrivastava wrote:
> Prabhu,
>
> Use the solution given by GoldenLance below.
>
> =SUBSTITUTE(A1,"ABC","#")
> *GoldenLance,*
> Thanks Man, This formula didn
Wouldn't this be much more easier, and apt?
=SUBSTITUTE(A1,"ABC","#")
On Jul 21, 9:27 pm, Prabhu wrote:
> Hi Anish,
>
> If it is "ABC12" and i need to replace the ABC form the cell then the
> formula will be ?
>
> Regards,
>
> Prabhu
--
-
If you are looking for an Excel formula, just use
=SUBSTITUTE(A1,"ABC","")
On Jul 21, 8:37 pm, "Daniel" wrote:
> HI,
>
> Sub ABCReplace()
>
> [A1].Replace "ABC", "#"
>
> End Sub
>
> Regards.
>
> Daniel
>
> De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
> la part
You are welcome.
Oh just noticed, out of curiosity, why is my message displayed after
Ashish's? I messaged it before!!
On Jul 21, 2:47 am, Natron wrote:
> Thanks to both Ashish and GoldenLance. Proble
Sub enterTotals()
myArr = Array(4, 5, 7)
For i = LBound(myArr) To UBound(myArr)
For Each rngArea In Columns(myArr(i)).SpecialCells(2, 1).Areas
With rngArea.Cells(rngArea.Cells.Count).Offset(1)
.FormulaR1C1 = "=SUM(" & rngArea.Address(1, 1, xlR1C1) &
")"
.Interior.Co
Unable to open file
Sam Mathai Chacko
On Jul 2, 1:45 pm, ali abbas wrote:
> Dear All,
>
> Attached a word 2007 file that contain a permanant/protected HEADER AND
> FOOTER.
>
> Can anyone know how to protect Header and Fooer in Word and Excel 2007 ?.
>
> Please check this file and GUIDE OT ALL M
Can you post an example? Not sure how a magic square works.
Sam Mathai Chacko
On Jul 2, 9:17 pm, karunanithi ramaswamy wrote:
> HI,
> I AM INTERESTED TO KNOW HOW TO AUTO FILL A 4X4 MAGIC SQUARE FOR ANY
> SUM WITH NO NUMBER REPEATS FROM A SERIES OF NUMBERS.
> THANKS IN ADVANCE
> -R.KARUNANITH
I mean copy without the curly braces. They are inserted by Excel when
you use the CSE keys
On Jul 2, 9:22 pm, Susan wrote:
> Hi Dilip,
> The formula only returned the first sheet, I need it to sum all sheets
> with reference to different ranges for the same criteria , and summing
> different rang
Hi Susan,
Here's two ways to solve it
1. With a simple combo of sumif formulas
=SUMIF(D2:D6,"C Wagner",E2:E6)+SUMIF(Sheet3!D11:D18,"C Wagner",Sheet3!
E11:E18)+SUMIF(Sheet2!D13:D23,"C Wagner",Sheet2!E13:E23)
2. With an array formula.
{=SUM((D2:D11="C Wagner")*(E2:E11)+(Sheet3!D11:D20="C Wagner"
I think this is fabulous. Another Excel MVP. Ashish, your dedication
and capacity to help has been exemplary. Keep up the great work, and
hope this title continues.
Cheers, Sam
On Jul 1, 9:02 pm, Umesh Soni wrote:
> Congrats.. Ashish
>
>
>
>
>
>
>
>
>
> On Fri, Jul 1, 2011 at 4:55 PM
sCellValue = ActiveCell.Text
On Jun 29, 1:03 am, Jon Kanas wrote:
> I have a macro which works down through all the cells in a column,
> parsing the contents of the cell for a particular text string. Here's
> the general outline:
> sCellValue = ActiveCell.Value
> CharCount = sCe
Try removing Application.Volatile
On Jun 27, 5:38 pm, crossy75 wrote:
> I have this bit of code in a spreadsheet - it is used with a formula
> to count the number of visible rows. This is useful in accounts where
> people hid things thus counting what you see isnt always what you get!
>
> 'Functi
I am blocked from attaching a file I guess. Not sure why.
So here's how you do it.
1. Hold CTRL and right-click on the button shape as well as the chart.
Go to format and properties, and then uncheck the lock
2. After deselecting the shapes, select only the button, again go to
format and properti
Selection.FormulaArray = "=RC[-4]:R[" & z & "]C[-4]&RC[-3]:R[" & z &
"]C[-3]&RC[-2]:R[" & z & "]C[-2]"
On Jun 19, 4:29 pm, hanumant shinde wrote:
> Hi Friends,
>
> I really need urgent help. i want to deliver smthing by tomorrow and i stuck
> here.
>
> Selection.FormulaArray =
> "=RC[-4]:R[46]C
ActiveCell.Rows("1:" & nrow).EntireRow.Select
On Jun 15, 4:11 pm, Hiren Sheth wrote:
> Hi
>
> I am getting following error while running below mention code.
>
> Please help.
>
> [cid:image001@01CC2B7B.1B6C0EA0]
>
> Dim nrow As Long
> ActiveCell.Select
> Cells(8, 1).Select
> ActiveCell.Offs
=SUM(OFFSET(A5,0,0,MATCH(MAX(A$5:A$999),A$5:A$999,1),1)), copy this to A2,
and then copy across to other cells
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://t
d Total AR 4 8 4
> 16 AD 1 2 3 CN 1 1 CE 2 3 5 DR 1 1 1 3 PA 1 1 4 1 7 Grand
> Total 1 6 17 11 35
>
>
>
>
>
>
>
> On Thu, Jun 9, 2011 at 7:08 PM, GoldenLance wrote:
> > Sub CurRegLoopHighLightBorderTopBot()
>
> > Dim objArea As Object
AD 1 2 3 CN 1 1 CE 2 3 5 DR 1 1 1 3 PA 1 1 4 1 7 Grand
> Total 1 6 17 11 35
>
> On Thu, Jun 9, 2011 at 7:08 PM, GoldenLance wrote:
>
> > Sub CurRegLoopHighLightBorderTopBot()
> >
> >Dim objArea As Object
> >
> >For Each objArea In Acti
Sub CurRegLoopHighLightBorderTopBot()
Dim objArea As Object
For Each objArea In ActiveSheet.UsedRange.SpecialCells(2).Areas
With objArea: .Rows(1).Cells.Interior.Color =
65535: .Rows(.Rows.Count).Cells.Interior.Color = 15773696: End With
Next
End Sub
On Jun 9, 10:42 am, Chan
Just a minor tweak to STDEV(i)'s code. Based on the original subject,
I might change sCriteria = "MyCriteriaText" to sCriteria =
ActiveCell.Value
On Jun 4, 2:24 pm, "STDEV(i)" wrote:
> please check and try this VBA code if it helps
>
> Sub DoSomething()
> * ' siti Vi*
> Dim w As Workshee
=VALUE(TEXT(A2,"MMDD"))
On May 29, 9:05 am, Prabhu wrote:
> Hi Friends,
>
> I have change my cell which in the date format ex.MMDD or DDMM to
> original number i.e smiler to
> type date.I have attached the sheet in which Column A shows date format and
> Column B is what i need to conv
=RANDBETWEEN(X,Y) Where X is a number greater than Y Example,
=RANDBETWEEN(1,500) will give random numbers ranging from 1 and 500
On May 29, 7:29 am, Suryaprasad wrote:
> Hi Experts,
>
> Need a formula or a code for the sample sheet Attached, this is about the
> generating a random based numbers
This is because MAC addresses are attached physically to the network
adapter hardware and not to the base computer itself. Computers with
multiple network adapters installed (sometimes called multihomed
systems) therefore possess multiple unique MAC addresses.
On May 28, 7:47 pm, Mahesh parab wro
=IF(OR(COUNTIF($B$2:$B$14,B2)=1,B2=""),TEXT(B2,"#"),"NO")
On May 28, 9:24 am, kannan excel wrote:
> Hi Excel Guru,
>
> i don't want to enter same mobile number. so i need matching or tracing
> formula.
>
> If I entered mobile number in a column means, how do i know? i already
> entered this mob
=IF(D2<=200,0,IF(D2<=500,7.5,12.5))
On May 12, 8:21 pm, Rajesh K R wrote:
> Hi Experts,
> Pls find the attached file and solve the issue regarding if function.
>
> Regards
> Rajesh kainikkara
>
> If function .xls
> 18KViewDownload
--
Use this in E2 and drag down
=DATE(2000+RIGHT(B2,2),LEFT(RIGHT(B2,4),2),LEFT(B2,LEN(B2)-4))
On May 12, 5:14 pm, Prabhu wrote:
> Hi friends,
>
> Plz help to change date format when downloaded report from 1st of every
> month to 9th of the month will be like DMMYY .
>
> I have no issue in changing
gt; *Compile error : syntax error*
>
> Regards,
>
> Saroj.
>
>
>
> On Thu, May 5, 2011 at 10:17 PM, GoldenLance wrote:
> > Thanks for highlighting my mistake C.G.Kumar. My apologies.
>
> > Please find the entire code sought by reader.
>
>
ting for
> password match by oversight.
>
> Hope you will be amend the same.
>
> Regards,
>
> C.G.Kumar
>
>
>
> On Thu, May 5, 2011 at 1:58 AM, GoldenLance wrote:
> > With ThisWorkbook
> > .ChangeFileAccess xlReadOnly
> >
With ThisWorkbook
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close 0
End With
On May 4, 5:35 am, Bob wrote:
> Can someone help with the code to delete a workbook if password is not
> entered within 3 tries. Thanks so much!
--
---
"<"&TEXT(TODAY(),"0")
Example =SUMIFS(A1:A5,B1:B5,"<"&TEXT(TODAY(),"0"))
GL
On May 3, 5:47 pm, Gerry wrote:
> Dear Experts
>
> I want to insert the NOW() or TODAY() expression into formula below
> instead of the 40666 value. However, it wont work when I do it. I've
> tried a number of differ
Chris,
Use
Sub ExportToPDF()
ActiveSheet.Range("A6:J15000").ExportAsFixedFormat
Type:=xlTypePDF, _
Filename:= _
Environ("USERPROFILE") & "\Desktop\Myoutput.pdf",
Quality:= _
xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=Fal
Hanumant,
Download the COM Add-in from
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en
and install. You should now be able to convert the range to PDF using
the vba
ActiveSheet.UsedRange.ExportAsFixedFormat Type:=xlTypePDF,
Assuming the time is a text, and not a time value, use
Range("L2").offset(0,1).resize(1,3).value =
split(Range(cellalpha).value,":")
On Apr 7, 5:11 pm, Speilman_54 wrote:
> I'm having an issue where I'm trying to separate a cell with time in
> it, for example the cell would conta 8:56:45 and I n
You might be using a 2007+ Excel file. Try using the extension .xlam
On Apr 6, 6:01 pm, Deepak Rawat wrote:
> Hi Friends,
>
> I have made some macros for some purpose and i want to use that in my
> all the excels.
> i got information from the net that is i change the extension of file
> to xla an
Try this
=IF(ISERROR(AVERAGE('Dept A'!B10,'Dept B'!B10,'Dept C'!
B10)),"",AVERAGE('Dept A'!B10,'Dept B'!B10,'Dept C'!B10))
On Mar 29, 7:52 pm, "John A. Smith" wrote:
> I have like tabs from different departments and need to summarize an average
> score by day. But if there were no scores in a p
STDEV(i), Timevalue will not work as it can never be greater than 24
hours. Use Value, and then change the format to [hhh]:mm:ss
On Mar 28, 7:37 pm, JsinSk wrote:
> If you can change the format of your times it should work. I used
> hh:mm:ss and it works.
>
> On Mar 28, 6:59 am, Bhushan Sabbani
Select ten cells starting from B2 to B11, starting from B2. Press F2,
and paste =LARGE(VALUE(RIGHT(B2:B886,8)),ROW(B2:B11)-1) and hit
CTRL+SHIFT+ENTER
On Mar 28, 3:59 pm, Bhushan Sabbani wrote:
> Dear All,
>
> I am facing the problem in the formula of the large to find the top 10.
>
> Here i am a
Just press CTRL and click on the chart, you will be able to see the
chart name in the name box on the top left corner..
Regards
On Aug 29, 4:24 pm, XpandeR <[EMAIL PROTECTED]> wrote:
> Hi Folks,
>
> I'm working on a macro that generates a chart on the basis of just
> refreshed table. But as
70 matches
Mail list logo
