Translate VBA Code

Aug 11, 2008

I have been sent a spreadsheet containing the following Macro, which is fair enough, only thing is, I don't understand the Macro, its too high for my extremely basic knowledge of VBA.

Private Sub CommandButton1_Click()
Dim rng As Range
Dim i As Integer
Dim sht1 As New Worksheet
Dim sht2 As New Worksheet
Set sht1 = Sheets(ActiveSheet.Range("D2").Value)
Set sht2 = Sheets(ActiveSheet.Range("D3").Value)
Set rng = sht1.Range("First_Line")
i = 0
Do Until sht1.Cells(rng.Row + i, 1) = ""
sht2.Range("Managers_Name") = sht1.Cells(rng.Row + i, 1)
sht2.PrintOut
i = i + 1
Loop
End Sub

translate this for me, step by step, as to what this Code is doing?

View 2 Replies


ADVERTISEMENT

Translate IF AND Formula To Code

Dec 14, 2006

How would I go about putting the following formula into VBA code? The .008 would be going in field O8. =IF(AND(M8>5,M8<10,N8>5,N8<10), 0.008)

View 5 Replies View Related

Translate Index / Match Function To VBA Code

Mar 17, 2013

How do I translate this to a vba code?

=INDEX('Workers List'!D3:D13,MATCH('Workers List'!C4,'Workers List'!C3:C13,0),1)

what i have done is

test = Application.Index(Sheets("Workers List").Range("C3;C13"), Application.Match(1, Sheets("Workers List").Range("D3:D13"), 0), 1)

But i keep getting an error, may i know whats wrong with it?

View 5 Replies View Related

VB Components Name Translate

Feb 12, 2014

Index of the VBProj.VBComponents("ThisWorkbook") component?

I need to refer to that component but not by its name because the file I use, goes to some users that have MS Excel installed with another language and their Excel translate the component's name in that language so

VBProj.VBComponents("ThisWorkbook") should be VBProj.VBComponents("[that-language-translation]")

View 3 Replies View Related

Translate Text From One Language To Another With VBA?

Feb 29, 2012

I have a spreadsheet with thousands of rows of text that I need converted from one language to several other languages.

Is there a macro I can use that taps into some free service, running down my page and giving a foreign language conversion for each cell?

[URL]

View 14 Replies View Related

Translate Formulas Between 12 Languages

Feb 9, 2007

It looks like the above has been inserted into my current workbook (by mistake). How do I delete it all (lots of the options have the remove option disabled)

View 3 Replies View Related

Translate Numbers To Text

May 8, 2007

I am trying to find a way to make a certain number equal a certain name. For example 1111 will equal Name1 in another cell and 1112 will equal Name2....

View 5 Replies View Related

Translate Yes Or No To Digit And Summarize In YTD Sheet

Apr 23, 2014

I have excel workwook with Month sheet Jan,Feb,Mar....Dec and YTD

In each monthly sheet i will have either "yes"or "No" in cell B66:K75.

Below is what i need to do in YTD sheet cell B66:K75. Each cell in TYD B66:K75 will have 12 digit number

Need to go thru each monthly sheet for each of the above cell and find if Yes or No and translate that into number (yes = 1, no = 0)

Example:

If B66 in Jan, Feb and Mar is Yes and B66 in Apr thru Dec is No then the number in YTD in cell B66=111000000000

View 5 Replies View Related

VBA To Translate Number Range To Category

Apr 9, 2009

I have 3 columns of data, column C has a number representing a length of time in months. I need to add a number to the end of each row (Column D), depending on the number in column C. Example:

If column C has a number between 0 and 3, the number to go in D would be 1. If column C has a number between 4 and 6, the number to go in D would be 2. Here are the list of possiblities (only 5):

0-3 = 1
4-6 = 2
7-9 = 3
10-12 = 4
12+ = 5

This will form part of a macro, so a VBA solution would be ideal. I thought of having a named range and looking up each row with a list of all possiblities. But i see that as rather crude. I'm hoping someone has a more logical solution they could share.

View 2 Replies View Related

Translate True Calendar Dates

Nov 23, 2009

I am trying to create a formula that can translate true calendar dates when a date is entered IE 2/15/09 if with the normal formaula, it alwways calulates 30 days or even on 31 day months.

I need th formula to calculate actual dates and know when there is a 31 versus a 30 day month or 28/29 for February.

View 9 Replies View Related

3D Linear Algebra. Translate To Best Fit Plane

Mar 20, 2008

I have several hundred points in cartesian x,y,z coordinates. I used an Excel add-in program to calculate the equation of best fit to the points. The equation is:

z=A*x+B*y+C
where (for this case):
A= 2.29091
B= -1.77338
C= -192.494

My objective is to define the several hundred x,y,z points in cartesian coordinates about a new cartesian coordinate system whose origin is the centroid of the data points and whose z plane (ie z=0) is equal to the best fit plane defined above (when referenced from the original coordinate system).

Using Pro/e (a cad program), I was able to create the new coordinate system using the x, y, and z intercepts from the best fit plane, and then was able to redefine the points about the new coordinate system. After the points were redefined in Pro/e, I was able to export the points back to Excel; however, I would prefer to only use Excel / VBA because I prefer the calculations be transparent for optimal accuracy and validation.

View 9 Replies View Related

How To Translate Number Of Months Into Future Date

Dec 9, 2009

I've developed a spreadsheet in Excel 2000 where one of the cells contains the number of months a quantity will last, calculated by taking the total amount and dividing it by the amount used each month (a constant).

I would like to have another cell where that number is translated into the future date that number of months represents, so that in addition to seeing how many months the total amount will last, one can see when the amount will be depleted.

For example, suppose it is December 9, 2009 (as it is now) and the number in the number-of-months cell is 6. Then, is there a way that when 6 is calculated, in another cell appears something like June 9 2010 or 6/9/2010 or 6/9/10 (assuming the computer's clock is correct)?

It would be nice if the formula would handle decimals too, such as the date for 6.4 months, but that's not essential.

View 9 Replies View Related

How To Extract Word In Dropdown Box And Translate To Another Cell

Mar 25, 2013

Lets suppose that in a drop down box I have the words : "I" "want" "to" "create" "a" " sentence".

Well what I would like to do is have each word as selected in the drop down box, placed in another cell creating a sentence.

If you picked the words in order the cell should read: I want to create a sentence or it could read any combination of these words depending in the order they were picked. So it could read: I sentence want to create a sentence if the words were selected in this order.

View 14 Replies View Related

Translate Data Of Dialed Numbers Into A Country Or Destination Name

Dec 21, 2008

I have some data for international dialed numbers, I need to translate this into a country or destination name.

for example if the dialled number begins with 001, the is the united states etc..
I have all of the required codes, but they all differ in length.
ie 0033 is spain but 003356 is a spain mobile.

I was thinking that i could use VLOOKUP to find the relevant text to display, but this does not work with the varying lengths of the codes.

View 9 Replies View Related

Excel 2013 :: How To Translate All Characters Of Selected Cells To Unicode

Apr 19, 2014

In Excel 2013, how do I translate all characters of selected cells to Unicode?

There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.

View 1 Replies View Related

Excel 2013 :: Translate All Characters Of Selected Cells To Unicode?

Apr 19, 2014

In Excel 2013, how do I translate all characters of selected cells to Unicode? There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.

View 10 Replies View Related

How To Translate Number Into Specific Letter Related To Number

Feb 3, 2014

How to translate a specific number into a specific letter related to the number itself.

I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.

So if the letter

B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course

B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course

P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course

P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course

and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.

The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.

and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course

and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.

View 9 Replies View Related

Translate Mathematical Formula To Formula

Oct 22, 2008

In cell C2 I want to compute the value of 5x^6 + SQRT of 6, where the value of x is located in cell A%. What formula would I need to enter into cell C2 to do this?

View 9 Replies View Related

2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others

Jan 27, 2009

I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..

View 2 Replies View Related

Cracked Vb Code: Prevent People From Accesing The Code I Protected The Code Blocking It From Visualization

Feb 8, 2007

I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.

View 8 Replies View Related

Pick Up The Vendor Code Of Part Code From Looking Highest Quantity Of Part Code

Mar 9, 2013

I have an excel file having part code,name,vendor and Qty ( Quantity ).

My problem is that I want to apply an excel formula to pick up that vendor code who have highest Qty of a part code.The condition is that S.No.should not be disturbed.This file is so large,but here I have taken an example,

View 2 Replies View Related

Calculations To Manual Before My Code Runs And Set It Back To Auto When My Code Is Done Running

May 12, 2009

I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?

View 2 Replies View Related

Excel 2010 :: VBA Code To Extract HTML Source Code Not Working On Google Sites

Dec 6, 2012

I have previously used the following code to successfully pull out IE webpage source code for string manipulation.

Its a crude example to demonstrate the principle:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

[Code] ......

However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"

The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......

The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.

Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?

Using :
MS Excel 2010
IE Explorer 8.0

View 1 Replies View Related

Have Code To Block Save But How To Keep Code In Workbook Without Saving The File

Aug 1, 2013

My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?

VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True

[Code].....

View 1 Replies View Related

Passing Variables And Values Between Sheet Code And Module Code

May 13, 2009

I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure: ...

View 11 Replies View Related

Code To Delete Rows Based On Status Code In Column

Jul 24, 2009

I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".

At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):

View 4 Replies View Related

Worksheet_Change Code: Determine How To Get The Code Below To Fire Whenever Cell J10 Is Populated

Jul 30, 2009

I am trying to determine how to get the code below to fire whenever cell J10 is populated and do nothing when cell J10 is not populated but I can't quite get it. (Cell J10 is manually changed and is not changed based off of a formula)

View 4 Replies View Related

Standard Way To Organize Sections Of Code With Code Tags?

Jul 25, 2014

Is there a standard way to organize sections of code with code tags? Due to the nature of my work, there is a lot of documentation involved. And, my spreadsheets rely heavily on VBA. These code tag sections will be going into a design specification, so that there is a layer of traceability from the document to the program. These are the general sections or item numbers that I've come up with.

1. Dimensions and Variables

2. Data

3. Processing the Data

4. Rules of analysis

5. Analysis

6. Formatting

I guess I'm looking for something standard like UML diagrams for DFDs, but with tagging code to break them up into sections?

View 4 Replies View Related

Using Code To Email A Sheet But Need To Strip The Code Before Sending

Feb 2, 2009

I am using the code below that I got off of these forums to email a particular sheet in my workbook, but I need to strip all of the VBA code and the command button from the sheet being sent.

View 14 Replies View Related

Understanding Code (code In Eglish Specially After The .Count /14-1)

Feb 6, 2009

Can someone explian to me this line of code in eglish specially after the .Count /14-1

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved