Re: $$Excel-Macros$$ Required help in Work Allocation VBA based excel file.
Hi
Unable to download files. pls could you mail this to my id.
dias.da...@gmail.com
Regards
On Sunday, 7 October 2012 14:58:10 UTC+5:30, Bullet wrote:
> *@ Krishna, Sir right now it's working fine.thanks a lot for all your
> kind support throughout. :) it's really make my job simpler.*
>
>
> *Thanks & Regards,*
> *Indrajit*
>
>
> On Wed, Oct 3, 2012 at 7:27 PM, Indrajit $nai > wrote:
>
>> *@ Krishna, waiting for your reply. :)*
>>
>>
>>
>> On Wed, Oct 3, 2012 at 1:13 AM, Indrajit $nai > > wrote:
>>
>>>
>>> *@ Krishna, no Sir; still showing the same error!*
>>>
>>>
>>>
>>> On Tue, Oct 2, 2012 at 9:26 PM, Krishna Kumar >> > wrote:
>>>
.Range("b" & LastRow).Resize(, 4) = Split(Me.TextBox2.Value & "|" &
Me.TextBox1.Value & "|" & Me.TextBox3.Value & "|" & Me.TextBox4.Value,
"|")
Kris
ExcelFox
--
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-...@googlegroups.com
.
To unsubscribe from this group, send email to
excel-macros...@googlegroups.com .
>>>
>>>
>>>
>>
>
>
--
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 http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Excel to Pdf
If you do a File-> SaveAs and select .pdfyou get an "options" button.selecting this button, you can select to publish "Active Sheets".This will create a pdf file of only the sheets selected. Another option would be to hide all the sheets except the ones you want to publish.then publish the "entire workbook". Here's the trick though:Recording a macro for the "Entire Workbook" or "Active Sheets" results in macros that look NEARLY identical. The difference is:ActiveWorkbook.ExportAsFixedFormatActiveSheet.ExportAsFixedFormat So, you can select the sheets you want to publish and use:ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:="C:\temp\Selected_Sheets.pdf", _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False or hide all the sheets except the ones you want to publish and use: ActiveWorkbook.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:="C:\temp\Displayed_Sheets.pdf", _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False 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: saravanan R To: excel-macros@googlegroups.com Sent: Tuesday, August 18, 2015 2:05 AM Subject: Re: $$Excel-Macros$$ Excel to Pdf Hi Paul, I found macros to export all the sheets to PDF, but I need to select only the specified sheets into PDF. If you have any references please share. It will be very useful for me to make this task bit easier.Thanks, Saravanan R On 13-Aug-2015 7:44 pm, "Paul Schreiner" wrote: Are you familiar with macros?This is fairly straight forward with a simple macro.Especially if you're using Excel2007, 2010 or 2013.Earlier versions it's a bit more difficult. 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: saravanan R To: excel-macros@googlegroups.com Sent: Thursday, August 13, 2015 5:41 AM Subject: $$Excel-Macros$$ Excel to Pdf Hi, I have an query about exporting each sheet(multiple sheets but not all) of the excel workbook to Pdf and save this PDF by name provided in the list. This task should be automated, because there are 20 to 45 sheets in the report. Please help me sort out this time consuming task. thanks in advance. Thanks & Regards,Saravanan RajuMob: +91 8807708842 +91 9449716334 -- 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 http://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
Re: $$Excel-Macros$$ not able to repeat code through all sheets in workbook
"Technically", it looks like you're not describing the problem accurately.The
problem isn't that it only works on the active sheet, the problem is that you
have a hidden sheet!
It just HAPPENS that in the list of sheets, you have:Summary ASummary BSummary
CCentre Acentre formats (original ) (which is hidden)Centre BCentre C
the Process_All macro skips the Summary sheets, and processes "Centre A"but
encounters an error when it tries to select the hidden sheet.
I simply added a test to see if the next sheet is visible.
also, In loops, it's a good idea to stop looking once you find what you're
looking for!"Exit For" will exit the loop once you've found the appropriate
value.
So, my resulting macros look like:-Option Explicit
Sub movedata()
Dim Lastcolumn As Integer, i, j As Integer, erow, ocell As Integer
Lastcolumn = Cells(Columns.Count, ActiveCell.Column).End(xlToRight).Column
For i = 7 To 34
If Cells(9, i) = 1 Then
Range(Cells(9, i), Cells(54, i + 34)).Select
Selection.Cut
Exit For
End If
Next i
For j = 7 To Lastcolumn
If Cells(8, j) = Cells(1, 5) Then
Cells(9, j).Select
ActiveSheet.Paste
Cells(19, j).Activate
ActiveCell.Value = 1
Exit For
End If
Next j
Columns("BY:BY").Select
Selection.Copy
Columns("G:AK").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
Sub Process_All()
Dim Sht
For Each Sht In Sheets
If ((Sht.Name <> "Summary A") _
And (Sht.Name <> "Summary B") _
And (Sht.Name <> "Summary C") _
) Then
If (Sht.Visible = True) Then
Sht.Select
Call movedata
End If
End If
Next Sht
End Sub
Also: I'm not sure what you are attempting to do with:
Lastcolumn = Cells(Columns.Count, ActiveCell.Column).End(xlToRight).Column
The Cells() method uses the form: Cells(row,column)You're using the number of
columns (16384) as the row numberand the column number of the currently
selected cell as the column.
Since your samples use less than 60 rows, I suspect that row 16384 will almost
always be blank.which means that using .end(xlToRight) should move to column
XFDwhich is column 16384.
You might as well simply set LastColumn = 16384
If instead, you're trying to find the column number of the last column with a
heading in row 8, you could use:
Lastcolumn = Cells(8, "G").End(xlToRight).Column
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: gargee singh
To: "excel-macros@googlegroups.com"
Sent: Tuesday, August 18, 2015 2:40 AM
Subject: RE: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
Hello Paul
Thanks again for your help. I had tried a similar code but couldn’t make It
work because it works onthe current active sheet but not on the other sheets ,
so I thought was doingsomething wrong. The sample file is attached herewith
along with the codes. For sampling I haveadded only 3 sheets but we will have
multiple centre sheets close to 80 /90 ata time and once we run the code I want
it to run on all sheets and move databased on the month mentioned in E1 .
Thanks and Regards,
Gargee Singh
Date: Mon, 17 Aug 2015 11:44:25 +
From: schreiner_p...@att.net
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
Are you saying that you want the macro to repeat for all sheets?
If so, you need to set up a loop to process all sheets.
Using your macro, you can create a separate macro like:
Sub Process_All()
Dim Sht for each Sht in Sheets if ((sht.name <> "Summary A") _ and
(sht.name <> "Summary B") _ and (sht.name <> "Summary C") _ ) then
sht.select movedata end if next Sht
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: gargee singh
To: "excel-macros@googlegroups.com"
Sent: Monday, August 17, 2015 3:28 AM
Subject: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
Hello Experts, I have this code which works fine except that I am not able to
repeat inon all sheets. When I edit the code it doesn’t perform the same
routine. I have multiple sheets in a workbook and I want this code to run on
allthe sheets except the first 3 (named – Summary A, Summary B, Summary C) Sub
movedata() Dim Lastcolumn As Intege
$$Excel-Macros$$ Urgent need for Front End .net Developer---New York, NY--LOCAL CANDIDATES ONLY
Hi Partner, Hope you are doing great … Please send resume to ma...@genuineitllc.com *Title : Front End .net Developer* *Location: New York, NY* *Duration: 6-12 months* *LOCAL CANDIDATES ONLY* JD:- We are seeking a developer who will be responsible for designing and developing web applications using Microsoft *ASP .NET MVC* and *Web API* technologies. The successful candidate will work on everything from front-end *HTML, CSS and JavaScript/JQuery* to the server side code and web services. *Skills and Requirements:-* ü Top notch programming skills ü *Client side HTML 5/JavaScript/JQuery/CSS development skills are essential* ü A track record to get work done on time ü In depth knowledge of HTML 5, CSS 3 (LESS/SASS), JavaScript and JQuery ü Advanced ASP .NET MVC skills ü *JQuery based custom controls (jQuery UI, Kendo UI or similar)* ü Up to date knowledge of ASP .NET MVC 4-5 and Web API 1-2 technologies ü Great understanding of .NET framework and OOP (principles, design patterns, SOLID, data structures) ü Being fluent working with *source control systems* like *Subversion and SVN*. Distributed source control experience is a plus. ü Experience with web services, REST (Web API/MVC) and SOAP (WCF/asmx). ü Experience in writing unit tests, including mocking and inversion-of-control/dependency injection ü Experience working with object relational mappers (ORM) such as NHibernate, Entity Framework or similar ü Excellent analytical and problem solving skills ü Bachelor of Science in Computer Science or similar ü 4+ years of solid development experience that meets the requirements Please let me know if you have someone. --- *With Warm Regards * *Madhu Roy* GenuineIT LLC *Certified Women Owned Business* Making IT Happen… [image: cid:image001.png@01CD0B34.DFB7EBF0] Ph: 832 539 3475 Fax: 281-605-5969 *E-Mail* :ma...@genuineitllc.com *Gmail *: *nandimadhu74 , madhu.genuineitllc* *YM *: madhu_recruiter Looking ahead to work with you for better Business prospects [image: cid:image004.png@01CDB5D0.74A7EE40] According to Bill S.1618 Title III passed by the 105th US Congress, this message is not considered as "Spam" as we have included the contact information. If you wish to be removed from our mailing list, please respond with "remove" in the subject field. We apologize for any inconvenience caused. [image: Description: stylepic_globe1]Please consider the environment before printing this e-mail -- 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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Client in New York, NY need Peoplesoft Financials Technical Developer
Hi all, Please send resume to ma...@genuineitllc.com *Title : Peoplesoft Financials Technical Developer* *Location: New York, NY* *Duration: 9-12 months* *LOCAL CANDIDATES ONLY* *JD:-* We are looking for PeopleSoft Technical Consultant with 5+ years of experience, who can help us with integration builds with SciQuest P2P Application. The consultants will participate in the design, development, testing and general support of the PeopleSoft Financials application. The candidates are required to be proficient in the following: •PeopleSoft Financials 9.0 •PeopleTools •Integration Broker •Application Engine •Application Designer •Component Interface •PL/SQL •SQL Plus •SQR •XML •Functional and Technical knowledge in Accounts Payable, Purchasing, General Ledger, Commitment Control, Project Costing and PeopleSoft Implementation Methodology •Writing Technical Specifications •Design, Development & Testing of Technical solutions --- *With Warm Regards * *Madhu Roy* GenuineIT LLC *Certified Women Owned Business* Making IT Happen… [image: cid:image001.png@01CD0B34.DFB7EBF0] Ph: 832 539 3475 Fax: 281-605-5969 *E-Mail* :ma...@genuineitllc.com *Gmail *: *nandimadhu74 , madhu.genuineitllc* *YM *: madhu_recruiter Looking ahead to work with you for better Business prospects [image: cid:image004.png@01CDB5D0.74A7EE40] According to Bill S.1618 Title III passed by the 105th US Congress, this message is not considered as "Spam" as we have included the contact information. If you wish to be removed from our mailing list, please respond with "remove" in the subject field. We apologize for any inconvenience caused. [image: Description: stylepic_globe1]Please consider the environment before printing this e-mail -- 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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Urgent Need!!! Microstrategy Developer----McLean VA
*Hi All,* *Please send resume to **ma...@genuineitllc.com* *Title : MicroStrategy Developer* *Duration: Long Term* *Location: McLean VA* *Interview: Phone and In-person* Project Tasks and Activities in support of: Reporting Tool Architecture, Design, and Development ü Ongoing development of central project management source and analytics and reporting tool, (*Datalign*) ü Coordination with, training of, and Communication with Stakeholders, SMEs, and tool users ü Define success criteria and measures to gauge project success ü Evaluate solution alternatives and solution design against requirements Standardization ü Support creation and ongoing maintenance of standard project data structure ü Support adoption of common definitions and terms --- *With Warm Regards * *Madhu Roy* GenuineIT LLC *Certified Women Owned Business* Making IT Happen… [image: cid:image001.png@01CD0B34.DFB7EBF0] Ph: 832 539 3475 Fax: 281-605-5969 *E-Mail* :ma...@genuineitllc.com *Gmail *: *nandimadhu74 , madhu.genuineitllc* *YM *: madhu_recruiter Looking ahead to work with you for better Business prospects [image: cid:image004.png@01CDB5D0.74A7EE40] According to Bill S.1618 Title III passed by the 105th US Congress, this message is not considered as "Spam" as we have included the contact information. If you wish to be removed from our mailing list, please respond with "remove" in the subject field. We apologize for any inconvenience caused. [image: Description: stylepic_globe1]Please consider the environment before printing this e-mail -- 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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Excel formula
I would appreciate if someone would look at this formula and tell me what I am doing incorrectly; I have a large spreadsheet called POS where I have different variables separated and I am trying to use IF,AND,ISERROR in nested IF statements and Excel doesn't like what I did. Please see my formula below '=IF(AND(ISERROR(VLOOKUP(A5388,POS!$A$2:$B$97,2,0)),"n/a",VLOOKUP(A5388,POS!$A$2:$B$97,2,0)),IF(ISERROR(VLOOKUP(A5388,POS!$H$5:$I$97,2,0)),"n/a",VLOOKUP(A5388,POS!$H$5:$I$97,2,0)),IF(ISERROR(VLOOKUP(A5388,POS!$O$5:$P$97,2,0)),"n/a",VLOOKUP(A5388,POS!$O$5:$P$97,2,0)),F(ISERROR(VLOOKUP(A5388,POS!$V$5:$W$97,2,0)),"n/a",VLOOKUP(A5388,POS!$V$5:$W$97,2,0)),F(ISERROR(VLOOKUP(A5388,POS!$AC$5:$AD$97,2,0)),"n/a",VLOOKUP(A5388,POS!$AC$5:$AD$97,2,0))) Thank you for any help you can give, MikeB -- 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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Excel formula
'=IF(AND(ISERROR(VLOOKUP(A5388,POS!$A$2:$B$97,2,0)),"n/a",VLOOKUP(A5388,POS!$A$2:$B$97,2,0)),IF(ISERROR(VLOOKUP(A5388,POS!$H$5:$I$97,2,0)),"n/a",VLOOKUP(A5388,POS!$H$5:$I$97,2,0)),IF(ISERROR(VLOOKUP(A5388,POS!$O$5:$P$97,2,0)),"n/a",VLOOKUP(A5388,POS!$O$5:$P$97,2,0)),iF(ISERROR(VLOOKUP(A5388,POS!$V$5:$W$97,2,0)),"n/a",VLOOKUP(A5388,POS!$V$5:$W$97,2,0)),iF(ISERROR(VLOOKUP(A5388,POS!$AC$5:$AD$97,2,0)),"n/a",VLOOKUP(A5388,POS!$AC$5:$AD$97,2,0))) i think u miss i so put I before f On Aug 19, 2015 3:32 AM, "Mike B" wrote: > I would appreciate if someone would look at this formula and tell me what > I am doing incorrectly; I have a large spreadsheet called POS where I have > different variables separated and I am trying to use IF,AND,ISERROR in > nested IF statements and Excel doesn't like what I did. Please see my > formula below > > > '=IF(AND(ISERROR(VLOOKUP(A5388,POS!$A$2:$B$97,2,0)),"n/a",VLOOKUP(A5388,POS!$A$2:$B$97,2,0)),IF(ISERROR(VLOOKUP(A5388,POS!$H$5:$I$97,2,0)),"n/a",VLOOKUP(A5388,POS!$H$5:$I$97,2,0)),IF(ISERROR(VLOOKUP(A5388,POS!$O$5:$P$97,2,0)),"n/a",VLOOKUP(A5388,POS!$O$5:$P$97,2,0)),F(ISERROR(VLOOKUP(A5388,POS!$V$5:$W$97,2,0)),"n/a",VLOOKUP(A5388,POS!$V$5:$W$97,2,0)),F(ISERROR(VLOOKUP(A5388,POS!$AC$5:$AD$97,2,0)),"n/a",VLOOKUP(A5388,POS!$AC$5:$AD$97,2,0))) > > Thank you for any help you can give, > > MikeB > > -- > 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 http://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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
RE: $$Excel-Macros$$ not able to repeat code through all sheets in workbook
Hello Paul,
A big thank you for your help. I had actually completely forgotten that
there was a hidden sheet at all and i kept thinking my code was not going
beyond the active sheet.
That's why we need an expert from time to time. :)
Thanks for the tip regarding last active column and help me improve the code.
It works wonderfully now. :) :)
Thanks and Regards,
Gargee Singh
Date: Tue, 18 Aug 2015 12:14:58 +
From: schreiner_p...@att.net
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
"Technically", it looks like you're not describing the problem accurately.The
problem isn't that it only works on the active sheet, the problem is that you
have a hidden sheet!
It just HAPPENS that in the list of sheets, you have:Summary ASummary BSummary
CCentre Acentre formats (original ) (which is hidden)Centre BCentre C
the Process_All macro skips the Summary sheets, and processes "Centre A"but
encounters an error when it tries to select the hidden sheet.
I simply added a test to see if the next sheet is visible.
also, In loops, it's a good idea to stop looking once you find what you're
looking for!"Exit For" will exit the loop once you've found the appropriate
value.
So, my resulting macros look like:-Option Explicit
Sub movedata()
Dim Lastcolumn As Integer, i, j As Integer, erow, ocell As Integer
Lastcolumn = Cells(Columns.Count, ActiveCell.Column).End(xlToRight).Column
For i = 7 To 34
If Cells(9, i) = 1 Then
Range(Cells(9, i), Cells(54, i + 34)).Select
Selection.Cut
Exit For
End If
Next i
For j = 7 To Lastcolumn
If Cells(8, j) = Cells(1, 5) Then
Cells(9, j).Select
ActiveSheet.Paste
Cells(19, j).Activate
ActiveCell.Value = 1
Exit For
End If
Next j
Columns("BY:BY").Select
Selection.Copy
Columns("G:AK").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
Sub Process_All()
Dim Sht
For Each Sht In Sheets
If ((Sht.Name <> "Summary A") _
And (Sht.Name <> "Summary B") _
And (Sht.Name <> "Summary C") _
) Then
If (Sht.Visible = True) Then
Sht.Select
Call movedata
End If
End If
Next Sht
End Sub
Also: I'm not sure what you are attempting to do with:
Lastcolumn = Cells(Columns.Count, ActiveCell.Column).End(xlToRight).Column
The Cells() method uses the form: Cells(row,column)You're using the number of
columns (16384) as the row numberand the column number of the currently
selected cell as the column.
Since your samples use less than 60 rows, I suspect that row 16384 will almost
always be blank.which means that using .end(xlToRight) should move to column
XFDwhich is column 16384.
You might as well simply set LastColumn = 16384
If instead, you're trying to find the column number of the last column with a
heading in row 8, you could use:
Lastcolumn = Cells(8, "G").End(xlToRight).Column
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: gargee singh
To: "excel-macros@googlegroups.com"
Sent: Tuesday, August 18, 2015 2:40 AM
Subject: RE: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
Hello Paul
Thanks again for your help.
I had tried a similar code but couldn’t make It work because it works on
the current active sheet but not on the other sheets , so I thought was doing
something wrong.
The sample file is attached herewith along with the codes. For sampling I have
added only 3 sheets but we will have multiple centre sheets close to 80 /90 at
a time and once we run the code I want it to run on all sheets and move data
based on the month mentioned in E1 .
Thanks and Regards,
Gargee Singh
Date: Mon, 17 Aug 2015 11:44:25 +
From: schreiner_p...@att.net
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ not able to repeat code through all sheets in
workbook
Are you saying that you want the macro to repeat for all sheets?
If so, you need to set up a loop to process all sheets.
Using your macro, you can create a separate macro like:
Sub Process_All()
Dim Sht for each Sht in Sheetsif ((sht.name <> "Summary A") _and
(sht.name <> "Summary B") _and (sht.name <> "Summary C") _) then
sht.select movedataend if next Sht 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.” - Jo
$$Excel-Macros$$ Excel Dashboard
Hi Everyone, Can someone please give me some site which gives really fancy dashboard illustration. We are tryng to build a couple of dahsboard but the "coolness" quotient is a lil low. Any help would be much appreciated. TIA -- 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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
