Re: $$Excel-Macros$$ Substitute a value of a cell in to a cell reference of a vba code
Paul, Thank you for the answer and simple explanation. I well understood the error and your code. Don, Thank you for the answer and it's working. Regards, Dilan On Wed, Sep 19, 2012 at 2:49 PM, Paul Schreiner wrote: > it looks like your first macro is irrelevent to your question. > You're wanting repairs to: > > > Private Sub salarynew() > > Dim r As Double > > For r = 1 To 20 > > ' Earlier E now should be value of cell I1 > > ' Earlier F now should be value of cell I2 > > If Cells(r, *"cells(1,"I")"*) = "Single" Then > > Cells(r, *"cells(2,"I")"*) = "8000" > > Else > > If Cells(r, *"cells(1,"I")"*) = "Married" Then > > Cells(r, *"cells(2,"I")"*) = "11000" > > End If > > End If > > Next > > End Sub > > -- > > > > the problem is: when you use "cells(1,"I")" you are EXPLICITLY > defining a string of characters. > > The beginning and ending quotes mean: "do not evaluate the contents, but > use these characters exactly as shown" > > You might as well say "this means nothing", because to the cells(r,c) > method, they mean the same thing. > > the string of characters do not represent a combination letter or numeric > values that represents a column. > > > > You COULD use: > > > > Col_Satus = Cells(1,"I").value > > Col_Salary = Cells(2,"I").value > > > > then use: > > If Cells(r,Col_Status).value = "Single" then > > Cells(r,Col_Salary).value = 8000 > > > > --- > > BTW: = "8000" is not the same as = 8000 > > > > = "8000" means to set the value of the cell to a text string "8000". > > > > Now, if the format of the cell is set to General, or Number, excel MAY > recognize the value as numeric and CONVERT it to a number. > > But I'd hate to base my salary on it. > > > > *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 > *- > > > -- > *From:* Dilan De Silva > *To:* excel-macros@googlegroups.com > *Sent:* Wed, September 19, 2012 5:03:33 AM > *Subject:* $$Excel-Macros$$ Substitute a value of a cell in to a cell > reference of a vba code > > Hi, > > Refer attached sheet(format 1). I want to fill the cells in one > column(Basic Salary) depend on cells value of a another column(Status). It > means if cell E1 has “single” then F1 should be 8000 and if cell E1 has > “Married” then F1 should be 11000.( I don’t want to use formulas) > > I wrote the below code to do that. > > > > Private Sub salary() > > Dim r As Double > > For r = 1 To 20 > > If Cells(r, "E") = "Single" Then > > Cells(r, "F") = "8000" > > Else > > If Cells(r, "E") = "Married" Then > > Cells(r, "F") = "11000" > > End If > > End If > > Next > > End Sub > > > > Now the columns are not always constant. It means Columns for “Status” are > E and D in format1 and format2 respectively. > > I want to use value of cell I1 and I2 for “Status and “Basic salary” > respectively. I will manually enter the column label for cell I1 and I2. > > I wrote below macro, but it is not working. I think the way I referred > the values of cell I1 and I2 is not correct. > > > > Please guild me to write this code perfectly. > > I’m not expert in macro, therefore there may be more easy methods to do > this task. However I want to do on this way since I’m can understand this > types of > > Codes well. > > > > Private Sub salarynew() > > Dim r As Double > > For r = 1 To 20 > > ' Earlier E now should be value of cell I1 > > ' Earlier F now should be value of cell I2 > > If Cells(r, *"cells(1,"I")"*) = "Single" Then > > Cells(r, *"cells(2,"I")"*) = "8000" > > Else > > If Cells(r, *"cells(1,"I")"*) = "Married" Then > > Cells(r, *"cells(2,
Re: $$Excel-Macros$$ Substitute a value of a cell in to a cell reference of a vba code
Dear vba, Thanks for the reply. You haven't pay attention for the latter part of of requirement. Can you change the macro to suit to following requirements.That is the key requirement of mine. Now the columns are not always constant. It means Columns for “Status” are E and D in format1 and format2 respectively. I want to use value of cell I1 and I2 for “Status and “Basic salary” respectively. I will manually enter the column label for cell I1 and I2. I wrote below macro, but it is not working. I think the way I referred the values of cell I1 and I2 is not correct. Please guild me to write this code perfectly. I’m not expert in macro, therefore there may be more easy methods to do this task. However I want to do on this way since I’m can understand this types of Codes well. Private Sub salarynew() Dim r As Double For r = 1 To 20 ' Earlier E now should be value of cell I1 ' Earlier F now should be value of cell I2 If Cells(r, *"cells(1,"I")"*) = "Single" Then Cells(r, *"cells(2,"I")"*) = "8000" Else If Cells(r, *"cells(1,"I")"*) = "Married" Then Cells(r, *"cells(2,"I")"*) = "11000" End If End If Next End Sub Thanks, Regards, Dilan On Wed, Sep 19, 2012 at 12:37 PM, VBA VABZ wrote: > Hi > > Replace Private with *Public & Put code in module to work.* > > PFA > > Cheers.. > > Rgds//Vabs > > > On Wed, Sep 19, 2012 at 2:32 PM, Dilan De Silva wrote: > >> Hi, >> >> Refer attached sheet(format 1). I want to fill the cells in one >> column(Basic Salary) depend on cells value of a another column(Status). It >> means if cell E1 has “single” then F1 should be 8000 and if cell E1 has >> “Married” then F1 should be 11000.( I don’t want to use formulas) >> >> I wrote the below code to do that. >> >> >> >> Private Sub salary() >> >> Dim r As Double >> >> For r = 1 To 20 >> >> If Cells(r, "E") = "Single" Then >> >> Cells(r, "F") = "8000" >> >> Else >> >> If Cells(r, "E") = "Married" Then >> >> Cells(r, "F") = "11000" >> >> End If >> >> End If >> >> Next >> >> End Sub >> >> >> >> Now the columns are not always constant. It means Columns for “Status” >> are E and D in format1 and format2 respectively. >> >> I want to use value of cell I1 and I2 for “Status and “Basic salary” >> respectively. I will manually enter the column label for cell I1 and I2. >> >> I wrote below macro, but it is not working. I think the way I referred >> the values of cell I1 and I2 is not correct. >> >> >> >> Please guild me to write this code perfectly. >> >> I’m not expert in macro, therefore there may be more easy methods to do >> this task. However I want to do on this way since I’m can understand this >> types of >> >> Codes well. >> >> >> >> Private Sub salarynew() >> >> Dim r As Double >> >> For r = 1 To 20 >> >> ' Earlier E now should be value of cell I1 >> >> ' Earlier F now should be value of cell I2 >> >> If Cells(r, *"cells(1,"I")"*) = "Single" Then >> >> Cells(r, *"cells(2,"I")"*) = "8000" >> >> Else >> >> If Cells(r, *"cells(1,"I")"*) = "Married" Then >> >> Cells(r, *"cells(2,"I")"*) = "11000" >> >> End If >> >> End If >> >> Next >> >> End Sub >> >> >> >> Thanks, >> >> Regards, >> >> Dilan >> >> >> >> >> >> >> >> >> >> >> >> -- >> Join official facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES (1120+ members already BANNED for violation) >> >> 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,
$$Excel-Macros$$ Substitute a value of a cell in to a cell reference of a vba code
Hi, Refer attached sheet(format 1). I want to fill the cells in one column(Basic Salary) depend on cells value of a another column(Status). It means if cell E1 has “single” then F1 should be 8000 and if cell E1 has “Married” then F1 should be 11000.( I don’t want to use formulas) I wrote the below code to do that. Private Sub salary() Dim r As Double For r = 1 To 20 If Cells(r, "E") = "Single" Then Cells(r, "F") = "8000" Else If Cells(r, "E") = "Married" Then Cells(r, "F") = "11000" End If End If Next End Sub Now the columns are not always constant. It means Columns for “Status” are E and D in format1 and format2 respectively. I want to use value of cell I1 and I2 for “Status and “Basic salary” respectively. I will manually enter the column label for cell I1 and I2. I wrote below macro, but it is not working. I think the way I referred the values of cell I1 and I2 is not correct. Please guild me to write this code perfectly. I’m not expert in macro, therefore there may be more easy methods to do this task. However I want to do on this way since I’m can understand this types of Codes well. Private Sub salarynew() Dim r As Double For r = 1 To 20 ' Earlier E now should be value of cell I1 ' Earlier F now should be value of cell I2 If Cells(r, *"cells(1,"I")"*) = "Single" Then Cells(r, *"cells(2,"I")"*) = "8000" Else If Cells(r, *"cells(1,"I")"*) = "Married" Then Cells(r, *"cells(2,"I")"*) = "11000" End If End If Next End Sub Thanks, Regards, Dilan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Code.xlsm Description: Binary data
Re: $$Excel-Macros$$ Macro to Calculate a total upon a condition
Paul,
Thanks for the help as usual. It's the one I expected.
dguillett,
Thank you for the solution and advice. I'm not very good in VB, therefore
my programme may inefficient. However I will learn the find method too.
Thank you very much.
Regards,
Dilan
On Thu, Aug 30, 2012 at 6:12 PM, Paul Schreiner wrote:
> It IS less efficient,
> but if you wanted to fix it...
> You're formula is NOT "=sum(G1:G&r)"
> but instead
> "=sum(G1:G" & r & ")"
>
>
>
> (because you want a string of characters, the VALUE of a variable, then
> another string of characters.)
>
>
>
> *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
> *-
>
>
> --
> *From:* dguillett1
> *To:* excel-macros@googlegroups.com
> *Sent:* Thu, August 30, 2012 10:17:10 AM
>
> *Subject:* Re: $$Excel-Macros$$ Macro to Calculate a total upon a
> condition
>
> Your method of looping is inefficient when compared to FIND.
>
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>
> *From:* Dilan De Silva
> *Sent:* Thursday, August 30, 2012 8:58 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Macro to Calculate a total upon a
> condition
>
> Dear Guillett,
> Thank for the solution. Your answer is perfect.
> I wrote below macro for the same project. However it's not working
> properly. I tried to put the sum formula to get the total. I think error is
> writing the upper cell reference(e.g:G50).
> Do you have any idea about getting the correct cell reference for my
> method.Please note that I'm concerning the answer you mentioned as"WRONG",
> it means I need the double of the correct total.
> ---
> Private Sub total()
> For r = 1 To 1
> If Cells(r, "C") = "COLLECTION" Then
> Cells(r, "G") = "=sum(G1:G&r)"
> ' For an example if r=50 then Cells(50,"G")=Sum(G50:G1)
> End If
> Next
> End Sub
> -
>
> Regards,
> Dilan
>
>
> On Thu, Aug 30, 2012 at 4:31 PM, dguillett1 wrote:
>
>> See attached using this macro for the CORRECT answer
>>
>> Option Explicit
>> Sub sumcollectionSAS()
>> Dim mf As Range
>> Dim cr As Long
>> Dim c As Range
>> Dim ms As Long
>> Dim firstaddress As String
>> Set mf = Columns("C").Find(What:="collection", LookIn:=xlValues, _
>> LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
>> If Not mf Is Nothing Then cr = mf.Row
>> 'MsgBox cr
>> With Columns("d:f")
>> Set c = .Find(What:="collection", LookIn:=xlValues, _
>> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
>> MatchCase:=False, SearchFormat:=False)
>>
>> If Not c Is Nothing Then
>> firstaddress = c.Address
>> Do
>> 'MsgBox c.Row
>> ms = ms + Cells(c.Row, "g")
>> Set c = .FindNext(c)
>> Loop While Not c Is Nothing And c.Address <> firstaddress
>> End If
>> End With
>>
>> 'MsgBox ms
>> Cells(cr, "g") = ms
>> End Sub
>>
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>
>> *From:* Dilan De Silva
>> *Sent:* Thursday, August 30, 2012 3:18 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* $$Excel-Macros$$ Macro to Calculate a total upon a condition
>>
>> Dear friends,
>> I want to programme a macro to calculate a total.
>> Refer *both sheets* of attached Excel sheet.
>> When the macro runs, it should search the word "COLLECTION".
>> After the macro found the word; the cell on Coloumn G of same row(In
>> this sheet cell G122) shoud have a formula to find total of all cells value
>> in column G from row 1 to a raw before the "COLLECTION"word located.
>>
>> Regards,
>> Dilan
>> --
>> Join official facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES (1120+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula
Re: $$Excel-Macros$$ Macro to Calculate a total upon a condition
Dear Guillett,
Thank for the solution. Your answer is perfect.
I wrote below macro for the same project. However it's not working
properly. I tried to put the sum formula to get the total. I think error is
writing the upper cell reference(e.g:G50).
Do you have any idea about getting the correct cell reference for my
method.Please note that I'm concerning the answer you mentioned as"WRONG",
it means I need the double of the correct total.
---
Private Sub total()
For r = 1 To 1
If Cells(r, "C") = "COLLECTION" Then
Cells(r, "G") = "=sum(G1:G&r)"
' For an example if r=50 then Cells(50,"G")=Sum(G50:G1)
End If
Next
End Sub
-
Regards,
Dilan
On Thu, Aug 30, 2012 at 4:31 PM, dguillett1 wrote:
> See attached using this macro for the CORRECT answer
>
> Option Explicit
> Sub sumcollectionSAS()
> Dim mf As Range
> Dim cr As Long
> Dim c As Range
> Dim ms As Long
> Dim firstaddress As String
> Set mf = Columns("C").Find(What:="collection", LookIn:=xlValues, _
> LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
> If Not mf Is Nothing Then cr = mf.Row
> 'MsgBox cr
> With Columns("d:f")
> Set c = .Find(What:="collection", LookIn:=xlValues, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False)
>
> If Not c Is Nothing Then
> firstaddress = c.Address
> Do
> 'MsgBox c.Row
> ms = ms + Cells(c.Row, "g")
> Set c = .FindNext(c)
> Loop While Not c Is Nothing And c.Address <> firstaddress
> End If
> End With
>
> 'MsgBox ms
> Cells(cr, "g") = ms
> End Sub
>
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>
> *From:* Dilan De Silva
> *Sent:* Thursday, August 30, 2012 3:18 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Macro to Calculate a total upon a condition
>
> Dear friends,
> I want to programme a macro to calculate a total.
> Refer *both sheets* of attached Excel sheet.
> When the macro runs, it should search the word "COLLECTION".
> After the macro found the word; the cell on Coloumn G of same row(In this
> sheet cell G122) shoud have a formula to find total of all cells value in
> column G from row 1 to a raw before the "COLLECTION"word located.
>
> Regards,
> Dilan
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 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) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
>
>
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 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) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members
Re: $$Excel-Macros$$ Run a Macro in a give time
Paul,
Thank you for the explanations. I think now I'm on right way to solve the
problem. Cheers!!
Regards,
Dilan
On Wed, Aug 8, 2012 at 3:30 PM, Paul Schreiner wrote:
> I may not be 100% accurate with this, because programming capabilities
> change, but...
>
> VBA is written WITHIN Applications.
> Visual Basic is written AS an Application.
> (VBScript would be a scaled-down version of VB)
>
> That is to say, if you want to run a VBA macro, you have to have
> an Application (like Word, Excel, PowerPoint)
> to provide the application "platform".
>
> If you don't want to open the workbook, then you're limited to Visual
> Basic, VBScript or some other Application-level programming language like
> C++
> Even with those, you'll need to have your database accessible.
>
> HOWEVER...
> You said: "I don't want to open the (work)book every day".
> Why not?
> You COULD have the scheduled task run at 4:00am every day and, if your
> macro determines that a specific criteria is met, send you an email.
>
> the macro itself would close the workbook, so you'll never actually "see"
> it open.
>
>
> *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
> *-
>
>
> --
> *From:* Dilan De Silva
> *To:* excel-macros@googlegroups.com
> *Sent:* Wed, August 8, 2012 1:48:16 AM
> *Subject:* Re: $$Excel-Macros$$ Run a Macro in a give time
>
> Paul,
>
> Thank for your reply. You answer is acceptable up to some extend. But I
> don't wan't to open the book every day, I just want to execute the macro
> only.
>
> ** **
>
> I have a data base of Insurance expiring dates. I wan't to get a message
> before the insurance expired.
>
> See the attached sheet for the macro.
>
> ** **
>
> Are they any method to open the workbook, when a insurance expiring?NO
> need to open excel sheet every day.**
>
> ** **
>
> Regards,
>
> Dilan
>
>
>
>
> On Tue, Aug 7, 2012 at 2:14 PM, Paul Schreiner wrote:
>
>> I would set up the macro as either a Workbook_open event or name the
>> macro Auto_Open.
>> then, create a Scheduled Task in Windows to open the workbook at 9:00am
>> daily.
>> I do this with several very large macros (and have them run at 11:00pm or
>> 4:00am so they are complete before I come in)
>> and they have worked relatively dependably for several years.
>>
>>
>> *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
>> *-
>>
>>
>> --
>> *From:* Dilan De Silva
>> *To:* excel-macros@googlegroups.com
>> *Sent:* Tue, August 7, 2012 5:42:48 AM
>> *Subject:* $$Excel-Macros$$ Run a Macro in a give time
>>
>> Friends,
>> I want to run a macro* every day in a give time(say 9.00AM)* when the
>> work book not open. Then open the work book and show a message if the given
>> conditions(say two cells have same value) fulfil. see the below written
>> macro for further explanation.
>> But the given below macro not working at the given time. However when I
>> run it manually (go to VB editor and click run button)it's working at any
>> time.
>> Please correct the error and explain.
>> --
>> Private Sub time2()
>> If TimeValue("09:00:00") Then
>> If Cells(1, "A").Value = Cells(1, "B").Value Then
>> MsgBox "Both are equal"
>> Else
>> End If
>> Else
>> End If
>> End Sub
>> ---
>>
>> Regards,
>> Dilan
>>
>> --
>> Join official facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES (1120+ members already BANNED for violation)
>>
>> 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.
&g
Re: $$Excel-Macros$$ Run a Macro in a give time
Paul,
Thank for your reply. You answer is acceptable up to some extend. But I
don't wan't to open the book every day, I just want to execute the macro
only.
** **
I have a data base of Insurance expiring dates. I wan't to get a message
before the insurance expired.
See the attached sheet for the macro.
** **
Are they any method to open the workbook, when a insurance expiring?NO need
to open excel sheet every day.**
** **
Regards,
Dilan
On Tue, Aug 7, 2012 at 2:14 PM, Paul Schreiner wrote:
> I would set up the macro as either a Workbook_open event or name the macro
> Auto_Open.
> then, create a Scheduled Task in Windows to open the workbook at 9:00am
> daily.
> I do this with several very large macros (and have them run at 11:00pm or
> 4:00am so they are complete before I come in)
> and they have worked relatively dependably for several years.
>
>
> *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
> *---------
>
>
> --
> *From:* Dilan De Silva
> *To:* excel-macros@googlegroups.com
> *Sent:* Tue, August 7, 2012 5:42:48 AM
> *Subject:* $$Excel-Macros$$ Run a Macro in a give time
>
> Friends,
> I want to run a macro* every day in a give time(say 9.00AM)* when the
> work book not open. Then open the work book and show a message if the given
> conditions(say two cells have same value) fulfil. see the below written
> macro for further explanation.
> But the given below macro not working at the given time. However when I
> run it manually (go to VB editor and click run button)it's working at any
> time.
> Please correct the error and explain.
> --
> Private Sub time2()
> If TimeValue("09:00:00") Then
> If Cells(1, "A").Value = Cells(1, "B").Value Then
> MsgBox "Both are equal"
> Else
> End If
> Else
> End If
> End Sub
> ---
>
> Regards,
> Dilan
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 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) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
>
>
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 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) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
>
>
>
$$Excel-Macros$$ Run a Macro in a give time
Friends,
I want to run a macro* every day in a give time(say 9.00AM)* when the work
book not open. Then open the work book and show a message if the given
conditions(say two cells have same value) fulfil. see the below written
macro for further explanation.
But the given below macro not working at the given time. However when I run
it manually (go to VB editor and click run button)it's working at any time.
Please correct the error and explain.
--
Private Sub time2()
If TimeValue("09:00:00") Then
If Cells(1, "A").Value = Cells(1, "B").Value Then
MsgBox "Both are equal"
Else
End If
Else
End If
End Sub
---
Regards,
Dilan
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Put items numbers to a BOQ
Dear friends, I'm looking a macro to put items numbers to a BOQ. My requirements as below. If Quantity = " " then Item nr = "" Else For page 1 to last page Item nr should be a letter (Starting from A on each page and if reached to Z within a page, next nrs should be AA, AB….). End See the attached file for further understanding my requirements. Regards, DIlan -- -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com 001 Highways Rev 0 copy.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Macro to calculate amount
*Paul,*
You are a star, Excellent. I got what I want. Thanks you very much for the
help.
Regards,
Dilan
*Rajan_Verma,*
Thanks for the help. However your macro gives the correct answer in
"Amount" Column, but as a numerical value(1250). I need the formula
(C5*D5)on the Amount cell.
Regards,
Dilan
On Tue, Apr 10, 2012 at 5:52 PM, Rajan_Verma wrote:
> Try this :
>
> ** **
>
> Sub CacluateAmount()
>
> Dim rngQuantity As Range
>
> Dim rngResult As Range
>
> Dim rngRate As Range
>
> Set rngQuantity = Application.InputBox("Please Select Range of Quantity",
> , , , , , , 8)
>
> Set rngRate = Application.InputBox("Please Select Range of Rate", , , , ,
> , , 8)
>
> Set rngResult = Application.InputBox("Please Select Range For Result", , ,
> , , , , 8)
>
> ** **
>
> If rngResult.Cells.Count = rngQuantity.Cells.Count And
> rngResult.Columns.Count = 1 And rngQuantity.Columns.Count = 1 Then
>
> rngResult =* Evaluate("=if(len(" & rngQuantity.Address & ")>0,("
> & rngQuantity.Address & ") * " & rngRate.Address & ", """")")*
>
> MsgBox "Done"
>
> Else
>
> MsgBox "Please select Correct Range, Cells are not same of give
> range"
>
> End If
>
>
>
> End Sub
>
> ** **
>
> ** **
>
> Rajan.
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *dguillett1
> *Sent:* Apr/Tue/2012 08:09
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Macro to calculate amount
>
> ** **
>
> If you REALLY need a macro, let me know.
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
>
>
>
> *From:* Dilan De Silva
>
> *Sent:* Tuesday, April 10, 2012 8:47 AM
>
> *To:* excel-macros@googlegroups.com
>
> *Subject:* $$Excel-Macros$$ Macro to calculate amount
>
>
>
> Dear experts,
>
>
>
> I need a macro for following requirement.
>
>
>
> Please refer the attached Excel sheet. If there is a value in the cell
> C5(Quantity), formula of cell F5(Amount) should be =C5*E5 otherwise cell F5
> should be blank.
>
> This should be repeat to each row which has value in Column C.
>
>
>
> Thank you.
>
> Regards,
>
> Dilan
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 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) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 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) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 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 q
Re: $$Excel-Macros$$ Macro to calculate amount
Paul, Thank you for the reply. If I used your answer, I have to paste it on each cell. Paste the formula in a work book with more than 100 pages is time consuming. Therefore I need to apply the formula to all cells in one click. Thank you. Regards, Dilan On Tue, Apr 10, 2012 at 5:22 PM, Paul Schreiner wrote: > There's no macro required. > you can use a simple formula: > =IF(C5>0,C5*E5,"") > then copy this down... > > > *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 > *--------- > > > -- > *From:* Dilan De Silva > *To:* excel-macros@googlegroups.com > *Sent:* Tue, April 10, 2012 9:47:29 AM > *Subject:* $$Excel-Macros$$ Macro to calculate amount > > Dear experts, > > I need a macro for following requirement. > > Please refer the attached Excel sheet. If there is a value in the cell > C5(Quantity), formula of cell F5(Amount) should be =C5*E5 otherwise cell F5 > should be blank. > This should be repeat to each row which has value in Column C. > > Thank you. > Regards, > Dilan > > -- > FORUM RULES (986+ members already BANNED for violation) > > 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) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > -- > To post to this group, send email to excel-macros@googlegroups.com > > -- > FORUM RULES (986+ members already BANNED for violation) > > 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) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > -- > To post to this group, send email to excel-macros@googlegroups.com > -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Macro to calculate amount
Dear experts, I need a macro for following requirement. Please refer the attached Excel sheet. If there is a value in the cell C5(Quantity), formula of cell F5(Amount) should be =C5*E5 otherwise cell F5 should be blank. This should be repeat to each row which has value in Column C. Thank you. Regards, Dilan -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Macro for a BOQ
Dear Vasant,
Thanks friend. It's good.
Regards,
Dilan
On Thu, Jul 7, 2011 at 8:14 PM, Vasant wrote:
> Try this...
>
> Sub test()
> Dim Wksht As Worksheet
> Set Wksht = ThisWorkbook.Worksheets(1)
> Lrow = Wksht.Range("C65536").End(xlUp).Row
> For x = 10 To Lrow
> If Wksht.Cells(x, 5) <> "" Then
> Vr = Wksht.Cells(x, 5)
> Wksht.Cells(x, 5).Formula = "=" & Trim(Str(Vr)) & "*$H$1"
> End If
> Next x
> End Sub
>
>
>
> On Thu, Jul 7, 2011 at 8:07 PM, Dilan De Silva wrote:
>
>> Dear Friends,
>>
>> I have a BOQ(Bills of Quantity) and I need to add a formula to each rate.
>>
>> The formula should be as follow.
>> =rate*(Constant cell reference)
>>
>> For an example;
>> The rate on cell E18 is 250.00 (see attached BOQ)
>>
>> It should like this after run the macro
>> '=250*($H$1)
>>
>> It need to repeat for each rate when macro runs.
>> Can any one help?
>>
>> Thanks in advance
>>
>> Regards,
>> Dilan
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Regards
>
> Vasant
>
> skype Id: vasantjob
> http://facebook.com/vasantjob
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ Macro for a BOQ
Dear Friends, I have a BOQ(Bills of Quantity) and I need to add a formula to each rate. The formula should be as follow. =rate*(Constant cell reference) For an example; The rate on cell E18 is 250.00 (see attached BOQ) It should like this after run the macro '=250*($H$1) It need to repeat for each rate when macro runs. Can any one help? Thanks in advance Regards, Dilan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel group.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Excel formula for a BOQ
Daniel
Thanks for the reply. But I couldn't solve my problem.
I created a macro from your code and rum it. Then there Is a error message
coming as "*Object variable or with block variable not set ".* How can I
overcome this problem. Could you please help me further.
Regards,
Dilan
On Mon, Mar 21, 2011 at 2:17 PM, Daniel wrote:
> Try :
>
>
>
> Sub test2()
>
> Dim c As Range, inRow As Long
>
> For Each c In Range([A4], Cells(Rows.Count,
> 1).End(xlUp)).SpecialCells(xlCellTypeConstants)
>
> If Cells(c.Row, 7).Value = "" Then
>
> inRow = Range(Cells(c.Row, 7), Cells(c.Row + 1000, 7)).Find("*", ,
> xlValues).Row
>
> Cells(c.Row, 7).Formula = Cells(inRow, 7).Formula
>
> Cells(inRow, 7) = ""
>
> End If
>
> Next c
>
> End Sub
>
>
>
> Regards.
>
>
>
> Daniel
>
>
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* Dilan De Silva
> *Envoyé :* dimanche 20 mars 2011 08:41
> *À :* excel-macros@googlegroups.com
> *Objet :* $$Excel-Macros$$ Excel formula for a BOQ
>
>
>
> Dear friends,
>
>
>
> Please refer the attached excel sheet.
>
>
>
> I want to get the Item nr and relavent percentage in to a one row for each
> item.
>
>
>
> For example,
>
> Item A and relavent percentage (105%) is in one row, but Item B and
> percentage(150%) not in a on row. Then, I want to get the 150% to above row.
>
>
>
> How can I do this by using a formula or macro?
>
>
>
> Thanks in advance
>
> regards,
>
> Dilan
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ Excel formula for a BOQ
Dear friends, Please refer the attached excel sheet. I want to get the Item nr and relavent percentage in to a one row for each item. For example, Item A and relavent percentage (105%) is in one row, but Item B and percentage(150%) not in a on row. Then, I want to get the 150% to above row. How can I do this by using a formula or macro? Thanks in advance regards, Dilan -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Excel.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
