VB syntax problem with referencing another tab

Hi. The  following macro works fine if I run it while the tab in question is 
open. If I try to run it with another tab open, I get a 400 error. I'm pretty 
sure it's just a problem with the way I'm referencing the tab. Any ideas on 
how I could make this work?


Sub Formatierung_Einverkauf()

Set sh1 = Sheets("Einverkauf")

    z = 4
    Do Until sh1.Cells(z, 1).Value = ""
        If sh1.Cells(z, 1).Value <> sh1.Cells(z + 1, 1).Value Then
        sh1.Rows(z).Select
            With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
            End With
        z = z + 1
        Else
        z = z + 1
        End If
        
    Loop

End Sub

Thanks a lot
0
Utf
3/4/2010 12:30:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
600 Views

Similar Articles

[PageSpeed] 14

This works fine for me

Sub Formatierung_Einverkauf()
Dim z As Long
    z = 4
    With Sheets("Einverkauf")
        Do Until .Cells(z, 1).Value = ""
            If .Cells(z, 1).Value <> .Cells(z + 1, 1).Value Then
                With .Rows(z).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlMedium
                    .ColorIndex = xlAutomatic
                End With
            End If
            z = z + 1
        Loop
    End With
End Sub


-- 

HTH

Bob

"Wombat" <Wombat@discussions.microsoft.com> wrote in message 
news:5D9A5851-9231-4E0A-B911-57A27549CB6C@microsoft.com...
> Hi. The  following macro works fine if I run it while the tab in question 
> is
> open. If I try to run it with another tab open, I get a 400 error. I'm 
> pretty
> sure it's just a problem with the way I'm referencing the tab. Any ideas 
> on
> how I could make this work?
>
>
> Sub Formatierung_Einverkauf()
>
> Set sh1 = Sheets("Einverkauf")
>
>    z = 4
>    Do Until sh1.Cells(z, 1).Value = ""
>        If sh1.Cells(z, 1).Value <> sh1.Cells(z + 1, 1).Value Then
>        sh1.Rows(z).Select
>            With Selection.Borders(xlEdgeBottom)
>            .LineStyle = xlContinuous
>            .Weight = xlMedium
>            .ColorIndex = xlAutomatic
>            End With
>        z = z + 1
>        Else
>        z = z + 1
>        End If
>
>    Loop
>
> End Sub
>
> Thanks a lot 


0
Bob
3/4/2010 12:37:52 PM
Hi,

There is vertually never any need to select cells to perform operations on 
them and this was the issue with this code, you tried to select on an 
inactive sheet. Do it this way

Sub Formatierung_Einverkauf()
Set sh1 = Sheets("Einverkauf")
    Z = 4
    Do Until sh1.Cells(Z, 1).Value = ""
        If sh1.Cells(Z, 1).Value <> sh1.Cells(Z + 1, 1).Value Then
        With sh1.Rows(Z).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
            End With
        Z = Z + 1
        Else
        Z = Z + 1
        End If
    Loop
End Sub
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Wombat" wrote:

> Hi. The  following macro works fine if I run it while the tab in question is 
> open. If I try to run it with another tab open, I get a 400 error. I'm pretty 
> sure it's just a problem with the way I'm referencing the tab. Any ideas on 
> how I could make this work?
> 
> 
> Sub Formatierung_Einverkauf()
> 
> Set sh1 = Sheets("Einverkauf")
> 
>     z = 4
>     Do Until sh1.Cells(z, 1).Value = ""
>         If sh1.Cells(z, 1).Value <> sh1.Cells(z + 1, 1).Value Then
>         sh1.Rows(z).Select
>             With Selection.Borders(xlEdgeBottom)
>             .LineStyle = xlContinuous
>             .Weight = xlMedium
>             .ColorIndex = xlAutomatic
>             End With
>         z = z + 1
>         Else
>         z = z + 1
>         End If
>         
>     Loop
> 
> End Sub
> 
> Thanks a lot
0
Utf
3/4/2010 1:14:10 PM
Reply:

Similar Artilces:

Problems saving a worksheet with Links
Does anyone know how I can resolve this issue ... I have a directory which contains 129 worksheets which have links to external data (in a Master Spreadsheet) -- I need to copy these files into a New Directory, but kee the Master Spreadsheet (which they are linked to) in the original location. If I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets moved to the New Directory (where the file does not exist), but if I open the worksheet (in the original directory/location) and Save As to the New Directory, the worksheet saved in the New Directory maintains its link t...

RPC over HTTP problem #3
Hi, All! My network configuration: DC1, DC2 and MX (MS Exchange 2003, sp1). All of them Windows Server 2003. What was done: In the registry on dc1 and dc2 was created a new key: "NSPI Interface protocol sequences" with value: ncacn_http:6004. MX was promoted to be a GC. Installed RPC over HTTP windows component. MX was changed to be RPC-HTTP back-end server. On the MX Default Web Site was installed cerificate from the local authority running on DC2. On the RPC virtual directory anonymous access and integrated windows authentication were disabled. In the registry of MX the key HK...

how do you place a spreadsheet(s) inside another spreadsheet
I am trying to setup a workbook with spreadsheets that contain other spreadsheets. Does excel allow this? if yes, how can I do it. Hi not 100% sure what you're after here ... a workbook can contain multiple worksheets. Each worksheet contains a grid of cells 256 columns wide & 65536 rows deep. This basic structure of excel can not be altered. Please post back with additional details on what you're trying to achieve and we might be able to suggest a solution. Cheers JulieD "ucastores" <ucastores@discussions.microsoft.com> wrote in message news:DDE44C32-...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

How to forward emails (unchanged) from one account to another
Assume I manage two different profiles and eMail accounts with my Outlook. How can I adjust in one of them that all arriving eMails should automatically be forwarded (unchanged !!) to the other account ? Sven "Sven Claasen" wrote: > Assume I manage two different profiles and eMail accounts with my Outlook. > > How can I adjust in one of them that all arriving eMails should automatically be forwarded (unchanged !!) > to the other account ? > > Sven > Automatically forward messages to another e-mail account - Outlook - Microsoft Office Online: http://of...

Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...

Insert an autofiltered range into another tab
I'm am having an issue when trying to insert a range from an autofilter on one sheet to another. The range has to be inserted above rows that have data in them. When I use the insert.shift:xldown, it only moves down the first column. TIA, Sub B_CreateTabs() Dim rngE As Range Dim lngLastRow As Long Dim mgrval, lobval, shtval As String mgrval = "myself" lobval = "dept" shtval = mgrval & "-" & lobval Windows("Mybook.xls").Activate Sheets(shtval).Select Sheets(shtval).Copy After:=Workbooks("Mybook.xls&quo...

Error- Project is used by another user -Please select another proj
Please see the subject line, when im trying to open a particular project. it gives me following error. I checked the current users, there are no users. Apart from me. There are some non PA users,i tried to remove them from activity. inspite of me removing them from activity, im geting the same error. Help !!! Ramakrishnan Hello Ram, There is a stranded user in PA000001 table. You need to clear this User/Project combination. Ajay "Ram" wrote: > Please see the subject line, when im trying to open a particular project. > it gives me following error. > > I che...

Send/Receive Problem
I am using Outlook 2002 on an XP platform. I cannot get Outlook to check for Email at regular intervals. I have the my Outlook set to Send and Receive all my accounts every 10 minutes but nothing happens. The only way I can receive Emails is by manually using the Send/Recv button or pressing F9. Can anyone offer any help. In case it is relevant I am using Norton Internet Security 2003. PWS Not sure it it Yor problem, but Outlook has some problems with Noroton Antivirus runing and chekking e-mails. As far as I know, Outlook may stop recieving e-mails from POP3 servers due to very le...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

Excel problem #3
I am attaching an excel file where i have a problem In the file are 2 sheets, Main & second I want to get data from second sheet to the main sheet by a formula by which the amount in the total column will be posted in the second sheet falling under various dates. I have done for 6 sept 2003 by way of example I do not know any formula by which i can do this automatically Please help me Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=444742 --- Message posted from http://www.ExcelForum.com/ Hi one way: ...

Sync net folder problem
I am sharing my calendar to my workmate with net folder. My PC is Win XP and Office 2000 and my workmate's is Win 98 and also Office 2000. I always find that My calender can't be updated from my workmate when I return office after I've taken my notebook for a few days. Can't net folder sync. data offline? Thx. your attention. Ken So Net Folders uses e-mail messages to send updates between computers so naturally you would have to be connected to your e-mail to get any updates. When you leave the office and are not connected you won't get any updates but as soon as ...

chart line style problem
I am making a scatter chart (with lines) in Excel 2007 under Vista. I can select a line style, for example, long dashes. However, if I try to change the axis (change from "automatic" to "fixed" on the horizontal axis), the line on the chart immediately becomes solid again. The legend still shows the proper dashing. I can get the dashing partly back by making the line thinner, but only where the variation is fastest - regions where the derivative is near zero are still solid even for thin lines. I'll appreciate any help! frank I was not able to reproduce this. Can you...

auto forward problems
I setup a 'contact' for 5 existing users in Exchange 5.5 Administrator. I give the contacts the desired SMTP address where they want their mail forwarded to. I set the corresponding 'contact' as the 'alternate recipient' for each of the 5 as detailed in Q255697. 2 of 5 work, the other 3 do not. When sending to each of the 5, 3 return undeliverable stating "A configuration error in the e-mail system caused the message to bounce between two servers or to be forwarded between two recipients." Any ideas? -adam Adam SK wrote: > I setup a 'con...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

Exchange 2003 with SP2 problems...
I installed the SP2 for exchange 2003 server tonight, and I'm getting some problems. I added the registry key to increase the DB size to 30GB per the instructions, but I don't get a confirmation in eventid 1216 as it says I should. In fact, the message in eventid 1216 looks mised up. See insert: The Exchange store '16384' is limited to First Storage Group\Mailbox Store (ATL-SBS) GB. The current physical size of this database (the ..edb file and the .stm file) is %3 GB. If the physical size of this database minus its logical free space exceeds the limit of First Storage Gro...

small problem
hi every body; i wrote a program that it has error ;plz help me :( using System; namespace ConsoleApplication45 { class Program { static void Main(string[] args) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("***********"); for (int i = 0; i < 1008; i++) { Console.BackgroundColor = ConsoleColor.DarkCyan; Console.Write(" "); } move(); // ***********error is for here***********************************************...

Active Directory/Exchange problem
All, Before I joined my current employer the admin here upgraded from Exchange 5.5 to Exchange 2000(Box A) and then added another Exchange 2000 box to the organisation(Box B)and migrated the data in Box A to Box B. Box B is now the working exchange server and Box A is no longer used. The problem is that if I actually shut down Box A I can add a new user to Active Directory but I am unable to modify a users email/smtp details. All mail can still be transferred with no problems which would lead me to believe that Exchange is Ok but there is some sort of Active Directory link between the two bo...

save as version 2003 problem
I'm working in vba in Access to create and save an Excel file. All's good except that one of the workstations this is runnign on is using Office 2007. I'm developing in 2003 and all the other workstatiosn they have are using 2003. It's very important that the files be saved in 2003 format. When I do this, it runs fine and saves as 97/95 objExcelBk.SaveAs sTempPath & sExcelFileName, xlExcel9795 ','56 = xl 2000/2003 I read online in a forum post that "56" is the correct code for saving as 2003 but that's when the code is written in 20...

Font problem with Office 2004 for mac
I have Tiger, whenever I try to Launch any Office program, as the the menu loads to 'optimizing font menu performance' It pops up with 'The font " " has been corrupted and should be removed'. It does this with MANY fonts, many of which I don't even have in my font folder. It does this every time during the program start, and most times even if I go through clicking ok 40-60 times it will sometimes freeze up anyway. Anyway to fix, get around this problem? Please email me at madefornothing@yahoo.com. Hi, this problem affects quite a few users, and there'...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

VB.NET Powerpoint Animation
Hi, Could someone please help me in applying an animation to an object in powerpoint through VB.NET. I'm only a beginner and have tried my best to figure it out with something like the following code which is two objects using one as the trigger. I managed to get it working in pptVBA but just can't get it to work in .NET. I keep getting an error COMException was unhandled. I cant seem to find any example of this on the internet to work with either. Any help with this would be extremely appreciated!!! Many thanks FustratedNaro25 Imports PowerPoint = Microsoft.Of...

Macro Security, Password Recovery & VB Code execution
I have found that if I set my macro security to high, VB code within (non-trusted) excel sheets do not execute... a microsoft security feature that ostensibly prevents non- trusted users from running VB The questions I have are : Is there a way to guarantee VB macro-execution using digital certificates or what-have-you ? Is there a way to detect, either within excel (well, not possible, it seems) or from outside, that the combo of users macro-security settings and signature status of a perticular excel file, WILL prevent VB from executing, if the file is attempted to opened ? ...

Hidden log on problem
On our XP Home laptop I have 2 users on the welcome screen, while my admin account uses (ctrl alt del) x2. All is fine until it goes into standby when I am in admin. Then on wake up the welcome screen comes back, but (ctrl alt del) x2 does nothing. I can't see a way to get back into my account without rebooting. Is there another way (opening one of the other accounts is as slow as rebooting)? Cheers, S On Mar 30, 9:26=A0am, "spamlet" <spam.mores...@invalid.invalid> wrote: > On our XP Home laptop I have 2 users on the welcome screen, while my a...

How to obtain enumerated attribute declarations from a DTD using MSXML & VB?
I have a VB (VB6, not .NET) application that reads an XML file (using MSXML v3.2 parser); the XML file contains a reference to an external DTD. The DTD has numerous enumerated attribute declarations like so: <!ATTLIST MyElement MyAttribute (One | Two | Three) "One" > The VB code needs to generate dynamic pick-list for all the attributes that appear in the DTD as part of a data-entry application; each dynamically created pick list should contain all the allowable values for the attribute as defined by the DTD (and presented in some kind of multiple choice control like a comb...