$$Excel-Macros$$ to change cell value in Column A daily

[Kat]

Hi all, 

I have an xlsm sheet and would like to change the cell content in Column A 
at 9am everyday. If cell value in column A equals to "Yes" today, then it 
needs to be changed to "No" automatically at 9am tomorrow without opening 
the file manually. May I know how can I get this done? Please see attached 
for my code.

Thanks in advance.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


run.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

$$Excel-Macros$$ Re: How can I add 2-6 characters to the front of numbers based ONLY on how long the number is?

[Paul Bevins]

Hi Karthik!

Thanks for the reply!

I liked the use of the LEN & AND statements, but your formula seemed longer 
than mine.
So I looked at yours again and realized I could ALSO use:

'=IF(Len(I2)<=5,CONCATENATE("AB",TEXT(I2,"0")),I2)

Now granted, it does allow for accidental numbers, like 15,654 (which 
doesn't exist in my database) to still come up with the AB prefix, however, 
so did my original formula. I also tried:

=IF(AND(Len(I2)<=5,I2<15001),CONCATENATE("AB",TEXT(I2,"0")),I2)

This actually strips the prefix away from numbers outside the range, making 
errors easy to spot. I can use conditional formatting to make those numbers 
error out in red.

Thanks for the Tip! 

Paulie
1725

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ facing challenge in week wise some - pls help

[Paul Schreiner]

I'm not sure where you're getting your original data,but based on the 
information you provided, you could:
- Add a "Week Number" column and use the function:  =WEEKNUM(C6,2)  copied down 
the list. (the ",2" specifies weeks starting with Monday)-Then, use 
=SUMIF($F$6:$F$36,$F$6+0,$E$6:$E$36)
=SUMIF($F$6:$F$36,$F$6+1,$E$6:$E$36)
=SUMIF($F$6:$F$36,$F$6+2,$E$6:$E$36)
=SUMIF($F$6:$F$36,$F$6+3,$E$6:$E$36)
=SUMIF($F$6:$F$36,$F$6+4,$E$6:$E$36)to calculate your "sums".
The "Week Days" text can be created by:=TEXT(C6,"ddd") & " To Sun"
Mon To Sun
Mon To Sun
Mon To Sun
="Mon To " & TEXT(C36,"ddd")
(see attached)Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
- 

On Wednesday, February 17, 2016 9:33 AM, Secret Shot  
wrote:
 
 

 Hi experts, 
Please in helping a dynamic formula to sum the value basis on Monday to Sunday 
week. where the first day is dynamic of every month. 
I have attached the excel file for explanation of problem . . . 
please help, quick help would be much appreciate. 

-- 
Pankaj Pandey
Bhopal-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


 
  

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


temp.xlsx
Description: MS-Excel 2007 spreadsheet

$$Excel-Macros$$ Re: 3 sets of data > 100 rows each, in same sheet: How to setup 1st 2 rows in each set as scroll headers

[Studiospeaker]

Paul, thank you for your help.We can close this topic.

On Monday, February 8, 2016 at 8:05:20 AM UTC-6, Studiospeaker wrote:
>
> Excel Experts: 
> I have 3 sets of data in the same sheet, (1) 100 rows x 18 columns (2) 110 
> rows x 15 columns, (3) 120 rows x 10 columns.
> I am trying to set up scrolling headers for each set of data. I have 
> researched a) splitting panes, b) page breaks, c) creating groups, and d) 
> converting each data set to a table, and e) pivot tables.
> But I am not able to figure out which of these will lead to setting up the 
> individual scrolling headers. Please help!  
> Thanks, Rama
>
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

$$Excel-Macros$$ facing challenge in week wise some - pls help

[Secret Shot]

Hi experts,

Please in helping a dynamic formula to sum the value basis on Monday to
Sunday week. where the first day is dynamic of every month.

I have attached the excel file for explanation of problem . . .

please help, quick help would be much appreciate.

-- 
Pankaj Pandey
Bhopal

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


temp.xlsx
Description: MS-Excel 2007 spreadsheet

$$Excel-Macros$$ Restrict users to edit macros but allow to view macro

[singla.kris...@gmail.com]

Hi,

I have created some macros and don't want users to edit them but they should be 
allowed to view coding. How to do this, tried VBA Project properties option but 
of no use as it lock project from viewing.

Appreciate your help
Thanks

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: VBA Code for Create Name Range

[amar takale]

Dear Mandeep,I want also VBA Code create unique sector name range in sheet

On Tue, Feb 16, 2016 at 5:38 PM, amar takale  wrote:

> Dear Mandeep Sir,
>
> Hi,I have small issue in this matter (all is OK in sheet as per my mind)
> but when code create name range then (Ctrl+F3) then it pick up both sector
> & company name data range & I required code to pick up only company name.
> Bcos when code pick up company name then I will create name range through
> data validation.
>
> Another small request can we extract unique sector name in column A in
> output sheet & then from B column code work as previous.
>
> Regards
> Amar
>
>
>
> On Mon, Jan 25, 2016 at 6:32 PM, amar takale  wrote:
>
>> Dear Mandeep sir,
>>
>> Suprb..
>>
>> Thank you all for your help. I greatly appreciated it
>>
>> Regards
>> Amar
>>
>>
>>
>> On Mon, Jan 25, 2016 at 1:24 PM, Mandeep Baluja 
>> wrote:
>>
>>> Check this out !!!  Regards, Mandeep baluja
 https://www.linkedin.com/messaging/thread/6086488646137958400
>>>
>>> https://www.facebook.com/groups/825221420889809/?fref=nf
>>>
>>>
>>>  Sub Creatnamedrange()
>>>
>>> Dim Sdic As Object
>>> Dim Nrows As Long
>>> Dim workrng As Range
>>> Dim col As Long: col = 1
>>>
>>> Nrows = Sheets("data").Cells(Rows.Count, 1).End(-4162).Row
>>> Set Sdic = CreateObject("Scripting.dictionary")
>>> Set workrng = Sheets("Data").Range("A1:B" & Nrows)
>>>
>>> For nrow = 2 To Nrows
>>> If Not Sdic.exists(Cells(nrow, 1).Value) Then
>>> Sdic.Add Cells(nrow, 1).Value, CStr(Cells(nrow, 1).Value)
>>> End If
>>> Next
>>> sarray = Sdic.keys
>>> For i = LBound(sarray) To UBound(sarray)
>>> workrng.AutoFilter field:=1, Criteria1:=sarray(i)
>>> workrng.SpecialCells(xlCellTypeVisible).Copy
>>> Sheets("output").Cells(1, col).PasteSpecial
>>> Paste:=xlPasteValues
>>> l =
>>> workrng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
>>> Set Rng = Sheets("output").Cells(2, col).Resize(l -
>>> 1, 2)
>>> ActiveWorkbook.Names.Add Name:=sarray(i), _
>>> RefersTo:=Rng
>>> Sheets("data").AutoFilterMode = False
>>> Application.CutCopyMode = False
>>> col = col + 3
>>> Set Rng = Nothing
>>> Next
>>>
>>> End Sub
>>>
>>> --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>> 4) Acknowledge the responses you receive, good or bad.
>>> 5) Jobs posting is not allowed.
>>> 6) Sharing copyrighted material and their links is not allowed.
>>>
>>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>> members are not responsible for any loss.
>>> ---
>>> You received this message because you are subscribed to the Google
>>> Groups "MS EXCEL AND VBA MACROS" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to excel-macros+unsubscr...@googlegroups.com.
>>> To post to this group, send email to excel-macros@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/excel-macros.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit