Excel Macro VBA Tip 9 - Enter Formulas in Excel with a Macro

I want to use an appliction to start my macro when the "enter" key is pressed. I typed in the code below, but it does not work. Any clues?
~~~~~(lockcells is the name of the macro I want to run)~~~~~~~


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.OnKey "~", "lockcells"

End Sub

I would like to use a macro to move to different cells , entering a value
into the cell before moving to the next one, can this be done?

HI Everyone,


I have few things in my mind and i want a macro to deliver that.

problem statement:

i am using a share excel workbook wherein all of us work in single sheet which is used for updating the information.We have kept the 1st column as "Analyst" column and in that, the names of the people who are going to feed the data. so the concerned person selects his name in dropdown of analyst column and starts feeding or inputting the data in the columns.

Now i have inserted a formula in 3 different cells in excel which would read and give me the result "true or false". that is the data what you have entered is going to be matched with the data required to be entered, if it mismatches. we get the resut as "false"

Now if suppose my formula shows "False", that implies i have done incorrectly, now i need a macro which would read that and if all the 3 cells show "false", then the macro should not allow me to feed the data in next line. Once i correct it and it shows true then i am allowed to enter data in next row.


This will help in correcting my mistake the next second and i do not have to wait for my accounts to be audited. As i can myself audit and correct the mistake before someone audits my accounts.


Awaiting Reply

Thanks

Im running a macro in excel that i didn't created but trying to figure out why is giving an error. The error that i get says "Type Mismatch" and that's it, but when i try to debug the macro it goes to this line

Code:

 
Dim iRow As Integer
iRow = CInt(Replace(sLastRange, "$K$", ""))

and i get the message. The macro is basically pulling data from store procs and with some formulas in the macro it display and format some fields in excel. The rest of the formulas are based on iRow but since it's failing here, then I don't get anything else. Any help will be
appreciated. I'm posting the line of code where is failing, I don't know much about macros, so I don't know if this is a correct format either, let me know if I have to put more code there.
Thanks in advanced.

Hi,
I want to these excel formula in macro does anyone have any idea?

"=IF(A$1=Sheet2!A:A,VLOOKUP(Sheet1!A$1,Sheet2!A:B,2,0),
IF(A$2=Sheet2!A:A,VLOOKUP(Sheet1!A$2,Sheet2!A:B,2,0),
IF(A$3=Sheet2!A:A,VLOOKUP(Sheet1!A$3,Sheet2!A:B,2,0),
IF(A$4=Sheet2!A:A,VLOOKUP(Sheet1!A$4,Sheet2!A:B,2,0),
IF(A$5=Sheet2!A:A,VLOOKUP(Sheet1!A$5,Sheet2!A:B,2,0))))))"

Also if i want A$'n" then how can i use it in Formulas as well as in Macro.

Hello, I have a problem.

The following code line fails:

Worksheets(1).Range("AA7") = "=IF(A5=0;0;1)"

The following code does not fail:

Worksheets(1).Range("AA7") = "IF(A5=0;0;1)"

so i guess the formula difference is why it does not work.

I get error 1004, and when i try to Evaluate("?IF(A5=0;0;1)") it returns
Error 2015


Why does my code fail?

Thanks!

-Anders

Hi , I am trying to add something to a macro, to make it calculate the formulas on sheet (Fund1) when new data gets imported there.

look at row 658, The latest price was copied there, but columns C to F are empty, I need those formulas to be recalculated for that row as well.
All columns have dynamic range names, is there a way to put that in a macro and make it calculate formulas?





would appreciate any help.
thanks

First time poster here,

I need help writing a macro which I will link to a button. Here is what I'm looking for:

I have 4 columns of data starting in rown B29 and ending in G29 (one of the columns is actually 3 merged columns which is populated by a dropdown list). 2 of the columns have formulas.

I would like the user to click the button, which will add a blank row with all the formatting from above (including the same drop down list and formulas). However, I would like to be able to add multiple lines and have the new line always appear at the end of the list.

As if that weren't complicated enough. I want to add another button to act as a 'reset' button which will delete the added rows, leaving me with the original blank row.

I tried recording my own macro but that doesn't work because the merged cells un-merge as the new row is added. And I can't get the new row to always appear at the bottom if I add more than one.

Is that enough info?

Brian

Hi there,
I am currently trying to record a macro that will help me bucketize marketing data that I work on. Even though most of the other nesting issues work correctly. I have trouble with one.
as the macro record the nesting function in more than 1 line, I am assuming that the code is broken, hence there is a syntax error.
Attached is the code.

Quote:

Application.CutCopyMode = False
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC5=""Less than 1 Million"",RC5=""1-3 Million"",RC5=""3-5 Million"",RC5=""5-10 Million""),""Under 10 Million"",IF(RC5=""10-25 Million"",""10-25 Million"",IF(RC5=""25-50 Million"",""25-50 Million"",IF(RC5=""50-100 Million"",""50-100 Million"",IF(RC5=""100-250 Million"",""100-250 Million"",IF(RC5=""250-500 Million"",""250-500 Million"",IF(RC5=""500 Million-1 Billion"",""500 Million-1 Billion"",IF(OR(RC5=""Over 25 Billion"",RC5=""1-10 Billion"",RC5=""11-25 Billion""),""Over 1 Billion"",IF(OR(RC5=""Confidential"",RC5="" ""),""Confidential"",""NA"")))))))))"
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC9>=1,RC9<=25),""Fewer than 25"",IF(AND(RC9>=26,RC9<=50),""26-50"",IF(AND(RC9>=51,RC9<=100),""51-100"",IF(AND(RC9>=101,RC9<=500),""101-500"",IF(AND(RC9>=501,RC9<=1000),""501-1000"",IF(RC9>=1000,""Over 1,000"",IF(RC9<1,""Confidential"",""NA"")))))))"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J152"), Type:=xlFillDefault
Range("J2:J152").Select
Columns("U:U").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC20=""Business Services"",RC20=""Financial Services"",RC20=""Insurance"",RC20=""Banking"",RC20=""Media"",RC20=""Language Services"",RC20=""Consumer Services"",RC20=""Retail "",RC20=""Real Estate"",RC20=""Telecommunications Services""),""Business Services"",IF(OR(RC20=""Industrial Manufacturing"",RC20=""Chemicals"",RC20=""Metals & Mining"",RC20=""Industrial Manufacturing"",RC20=""Optics & Photonics"",RC20=""Security Products & Services""),""Industrial Manufacturing"",IF(RC20=""Aerospace & Defense"",""Aerospace & Defense"",IF(OR(RC20=""Food and Drink"",RC20=""Agriculture""),""Food and Drink"",IF(OR(RC20=""Automotive & Transport"",RC20=""Transportation Services""),""Automotive & Transport"",IF(OR(RC20=""Biotech & Pharma"",RC20=""Health Care""),""Biotechnology"",IF(OR(RC20=""Energy & Utilities"",RC20=""Waste Management Services""),""Alternative energy"",IF(OR(RC20=""Computer Software"",RC20=""Computer Hardware"",RC20=""Computer Services"",RC20=""Electronics""),""Information Technology"",IF(OR(RC
20=""Consumer Product Manufacturing"",RC20=""Security Products & Services"",RC20=""Telecommunications Equipment""),""Consumer Product Manufacturing"",IF(OR(RC20=""Associations and Government"",RC20=""Membership Organizations"",RC20=""Foundations"",RC20=""Government""),""Associations and Government"",IF(RC20=""Construction"",""Construction"",IF(RC20="""",""Empty Field"",""NA""))))))))))))"
Range("U1").Select
ActiveCell.FormulaR1C1 = "Industry class"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U152"), Type:=xlFillDefault
Range("U2:U152").Select
End Sub


Now I tried line break with " _" and then proceeded the next line with " &" but I still have challenges. Any help would be appreciated.

Thanks

Hi! Everyone!

Statement of Situation 1:

I have this log files (.txt) that I've wanted to import with delimiter (comma(,), dash(-),semicolon( and space( ). I used Macro Recorder to write the code for me using the Input External Data. Code Below:

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;J:\PVLEXTRACTION_SVM\[1] CONNECTIVITY_FILES\MN_CP\cp.usn_log", _
Destination:=Range("A1"))
.Name = "openfilename"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = "-"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Question1:
1) How will I put the code line "getopenfilename" so it won't open the same file and import the same data from the file?

Statement of Situation 2 & Question 2:

After importing the files to excel, I want to format all contents of the cell into a "TEXT". Below is the code I try to develop and working, but the problem is, I did not know how can i stop the process of formatting on the last cell of the last data on the column and row. If I will used the "Range("A65536").End(xlUp).Range("$BX$1").Select" my data consists of variable number of blank cell which i cannot delete for it is in-line by row with my data and to maintain the other data's of the other cells in the sheet.

'Formatting
'Calculate the Next Row and Column to Test for value and replace value
NextRow:
x = x + 1
y = y + 1
'Select on the sheet the cell to be test
xrows = Rows(x).Columns(y).Select
Selection.NumberFormat = "@"
'minus the column by 1 to advance row by 1
y = y - 1
'Store the value of selected cell in CellSection
CellSection = (Selection.Value)
'Test the value of Stored CellSection if its equal to 36617
If CellSection = "36617" Then
'If equals to 36617 replace it with 4/0 text
ActiveCell.FormulaR1C1 = "4/0"
'If not terminates if statement and proceed to next command
End If
'Test the value of stored CellSection if its equal to 36526
If CellSection = "36526" Then
'If equals to 36526 replace it with 1/0 text
ActiveCell.FormulaR1C1 = "1/0"
'If not terminates if statement and procedd to next command
End If
'Test the value of stored CellSection if its equal to 36557
If CellSection = "36557" Then
'If equals to 36557 replace it with 2/0 text
ActiveCell.FormulaR1C1 = "2/0"
'If not terminates if statement and proceed to next command
End If
'Test the Value of Store CellSection if its equal to space
If CellSection = "" Then
'If equal to space call command to go to the next statements
GoTo BlankCount:
'If not then terminate the existing command to proceed to the next command
Else
GoTo NextRow:
End If
GoTo NextRow:

'I used this BlankCount up to 200 since BlankCells vary in numbers and i cannot delete coz I'm maintaining the intact of the data's from the original importing process.
BlankCount:
BlankCounter = BlankCounter + 1

If BlankCounter = "200" Then
BlankCounter = Empty
GoTo ColNext:
End If
GoTo NextRow:

ColNext: 'format next column not exceeding 150 cells
CellCounter = CellCounter + 1
y = y + 1
x = Empty
If CellCounter = "130" Then
Msgbox("WorkSheeet Formatting Have Been Completed")
Exit Sub
end if
End Sub

Any Help Will be highly appreciated!

Thank you and Best Regards,

Mykel