Finetune my macro

pbbspl

Board Regular
Joined
Apr 13, 2004
Messages
51
Halting macro

Hi Board,
I have written this macro which on started pastes the data in r35c27 and calculates the formulaes and checks if a1 and a2 values are >=1, then it creates a file in specified folder with the values of a1 or a2 as required and sends a key storke Y or N to my backoffice software. this code has been written by me reading/uderstanding other codes.

This code works fine but some times doesnot give me desired results.Can anybody help with a correct way to write this code in a proffesional touch.

thanx in advance.
bob



Code:
Sub Macro1()
Application.Goto Reference:="R35C27"
ActiveSheet.Paste
If ActiveWorkbook.Worksheets(1).Range("a1").Value >= 1 Then
Const sSheet As String = "Sheet1"
Dim wsh As Excel.Worksheet
Dim sPath As String
Set wsh = ThisWorkbook.Worksheets(sSheet)
With wsh
sPath = "c:\routersales\rs.txt"
Open sPath For Output As #1
Print #1, .Range("A1").Value
Close #1
Application.SendKeys ("%{tab}")
Application.SendKeys ("{y}")
End With
Else
If ActiveWorkbook.Worksheets(1).Range("a2").Value >= 1 Then
Set wsh = ThisWorkbook.Worksheets(sSheet)
With wsh
sPath = "D:\modemsales\ms.txt"
Open sPath For Output As #1
Print #1, .Range("A2").Value
Close #1
Application.SendKeys ("%{tab}")
Application.SendKeys ("{y}")
End With
Else
Application.Goto Reference:="R35C30"
ActiveSheet.Paste
Macro3
End If
End If
End Sub
 
Sub Macro3()
Application.SendKeys ("%{tab}")
Application.SendKeys ("{N}")
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Answering your query in a general way ............

Firstly no-one here will attempt to read your unformatted code. It is hard enough trying to understand someone else's code even when we can read it.
You also need to add a few comments to show what your code is supposed to do.

If you get into the habit of doing this it does not take a lot longer - and you will be able to return to your code months later and make changes in minutes.

When using SendKeys it is often necessary to stop the code for a second or so to allow time for things to happen on screen. Although I have found a better method for saving bitmaps since I posted code here, it does show the principle.
http://www.mrexcel.com/forum/showthread.php?p=2136593
 

Watch MrExcel Video

Forum statistics

Threads
1,122,552
Messages
5,596,801
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top