# Translate IF AND Formula To Code

Dec 14, 2006How 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 RepliesHow 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 RepliesADVERTISEMENT

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?

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?

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 RelatedIndex 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]")

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]

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 RelatedI 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 RelatedI 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

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.

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.

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.

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.

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.

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.

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.

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 RelatedHow 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.

I am using the Networkdays function to calculate the number of working days between a date entered in A1 and a date in B2 and I have a range of holidays called 'holidays' and then have the result placed in E1.

View 9 Replies View RelatedI have a spreadsheet which contains dates (amongst other info) of when a specific form was completed for a client. We have 'date windows' which these should fall into. I'm really struggling with how to work this one out and whether to use nested IF's or try some kind of code (although my vb knowledge kind of stops after recording a macro). Bascially I need to know for each 'date window' if the window has been met or missed. There may be up to 30 different dates per person (in my example sheet I have up to 9).

View 2 Replies View RelatedBelow is my requirement and i have to convert formulas into vba code:

If dt = "A0" Or dt = "A1" Then

Sheet1.Range("K").Formula = "$J/$ I" ' Formula is k=J/I

Sheet1.Range("K:K" & last_row).FillDown

Sheet1.Range("L").Value = "20.00%"

Sheet1.Range("L:L" & last_row).FillDown //Formula is L=20% for now i have just hardcoded the value there.

Sheet1.Range("M").Value = "=($K-$L)* 100" 'Formula is M=(K-L)/*100

Sheet1.Range("M:M" & last_row).FillDown

End If

How can I write this formula in vba code:

RevName = "Randolph Wakabayashi"

Range("A1").Value =LOWER((Left(Right(RevName, Len(RevName) - Find(" ", RevName, 1)), 5)) & Left(RevName, 2))

Trying to use a standard formula based on a variable workbook name. The INDIRECT solution suggested worked fine until I realised that I'd need to have the additional workbooks open in order to return the results.

Variable Workbook name in formula?

The reason I wanted to use a standard formula was so that I could use a piece of code I've written that would simply copy the formula from one cell and paste it accross and down for all cells within a range of rows and columns. Seeing as the formula now needs to be unique per column I need to change the code to copy the entire row of formula down instead of the one formula down and across. Here's my original code that copies a formula from B2 and pastes it down to the last row and across to the last column:

Code:

Sub CopyFormulaDown()

Dim lngLastrow As Long

Dim rngTargetStart As Range

Dim rngTargetEnd As Range

lngLastrow = Sheet3.Range("A65526").End(xlUp).Cells(1, 1).Row

[Code] .....

What I assume I need is to change the Range("B2") in the last line to Range("B2:???"), where ??? is the last column eg AA2. I'm not sure how to do this dynamically though.

I have a working formula that I want to convert to vba code... I only want to store the formula result in each cell in my range, not the formula itself. It is an ARRAY formula (entered with CTRL + SHIFT + ENTER) and it would be ideal if it could follow R1C1 format to keep it relative for each cell.

Here is the formula as it's entered in my spreadsheet:

=INDEX(PerfMetricTbl!$U$2:$U$250000, MATCH(1, (PerfMetricTbl!$A$2:$A$250000=$A2)*(PerfMetricTbl!$D$2:$D$250000=$D2)*(PerfMetricTbl!$Q$2:$Q$250000=$Q2)*(PerfMetricTbl! $R$2:$R$250000=Z$1), 0)))

I am not super familiar with using INDEX/Match combos which is why I am struggling with this one...

The end result should be:

For each cell in myRange

cell.value = 'Formula result here

Next cell

-OR-

If possible to fill the range in one step:

myRange.value = 'Formula Result Here, utilizing R1C1 relative reference... (this may be a stretch given the range is 55krows x 18 columns)

The problem at hand is I need to fill a range of approx. 999,000 cells with values and the values are a VLOOKUP with 4 matching criteria. So returning the value from Col U on my LookupTbl when the criteria in Col's A, D, Q, and R are an exact match to my values in A, D, Q, and Z:AQRow1.

I have the following code which works plus a formula in cell range G5:G123....how do i add the the formula to the VBA code

Formula =IF(F5"".F5< today () +3 )

I have two worksheets: "Report" & "Hou Jobs". In Report worksheet, I need a code equivalent to following formula so that it returns value in cell D5.

If the values in cells B5 & B8 change, the value should update itself.

=VLOOKUP(B5,'HOU Jobs'!A4:B667,2,FALSE)

I have made some UDFs (custom functions) using VBA, when the problem is that they don't update automatically like standard functions. I have numerous tickboxes which cause text to become bolded, and then my function counts the total, only including bolded text:

Function CountGamerScore() As Long

Dim i As Integer

Dim strScore As String

Dim intDigit As Integer

i = 6

For i = 6 To 52

strScore = "L" & i

If Range(strScore).Font.Bold = True Then

intDigit = Left(Range(strScore).Value, Len(Range(strScore).Value) - 1)

CountGamerScore = CountGamerScore + intDigit

End If

Next i

End Function

However, if I untick or tick a checkbox, the bold and unbold works fine (done in another sub, but the function does not update the value in the cell for total.

Therefore, is there any VBA code that will tell Excel to recalculate formulae??

How can I program a variable 'rowcountnumber', that would count the number of active rows, starting from row 10 ?

View 8 Replies View RelatedEvent type Houring rate Hours worked Total amt to be paid

A 30 2 XXX

B 10 1

C 20 4

Using this example, i wish to add a formula using VBA in cell XXX. Hence, the formula in EXCEL in cell xxx would show =b2*c2+b3*c3+b4*c4.

If the user clicks on a cell within a specified range ("DataEntryWindow") which contains formulas, I want the formula RESULT to be copied on to the formula bar for that cell (not in to a cell) - the user can then press enter to replace the formula with the result, or escape to cancel

- you can do this manually by copying the cell as values to another cell, then copying that value to the clipboard letter by letter, then clicking on the original, clicking F2, selecting all the formula text, then pasting the clipboard!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("DataEntryWindow")) Is Nothing Then

curval = Target.Value

Application.SendKeys "{F2}"

'Application.SendKeys "???" ' some keyboard combination to go left, left, left

Application.SendKeys curval

End If

End Sub

Needless to say, it doesn't work. sendkeys seems to do nothing at all. is there an object in worksheet for the formula bar that i can paste to?

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