Read or Get Data from Worksheet Cell to VBA in Excel

Hi please help me with this one:

The code below should read all value from cell P1:P4 and implement it in an autofilter…Thanks for your help in advance

Sub c1()
Sheets(“Master”).Select
MsgB Range(“P1”)
MsgB1 Range(“P2”)
MsgB2 Range(“P3”)
MsgB3 Range(“P4”)

Dim i As Long
For i = 2 To 13
With Sheets(i).Activate
ActiveSheet.Range(“$Y$2:$Y$2999”).AutoFilter Field:=1, Criteria1:=Array(MsgB, MsgB1, MsgB2, MsgB3)
Range(“A1”).Select
On Error Resume Next
End With
Next i
End Sub

Hi,

I have below requirement:

Sheet1: Two cols
Col1 Col2
A 1
B 2
C 3

Sheet2: Two Cols
Col1 Col2
A 2
D 3
C 4

Read Col1 -Lets say A from sheet 1, (loop thru each row in Col1), search the first value of Col1 which is A in sheet2 and get its corresponding Number which is 2 and write this in sheet1 in Col3.

Sheet1: 3 cols
Col1 Col2 Col3
A 1 2
B 2 NA
C 3 4

Can Anyone please help with this question.

Thanks so much!

But Friend, It does not work throws error and i corrected the uotes which is in different format in the excel when i tried copy paste from here, after corrected its throws run time error like Run time error : 9 subscript out of range like that,.

hi

I have the names of all the workbooks which are listed on a worksheet called”filelist” saved in a workbook called “Read property and transaction data with macro to be run 070715.xlsm”. I need to get the workbook to open for all the the names listed in filelist worksheet and go to the tab called “GL Transactions” and retrieve a value from call AB12 and save it in the workbook “Read property and transaction data with macro to be run 070715.xlsm” under a tab called GL Total. How can I record a macro for this?

I have a excel file contains in the cell AI one, A2 two, A3 three,… I want to copy the value in another excel one in the sheet1 cell A1, two in the sheet2 cell A1, three in the sheet3 cell A1,…

I want to select and print the worksheets in a workbook whose cell, (I22), contains a value greater than one (1).

Thank you

Sub main()
Dim Newsheet As Worksheet
Dim val As Double
Dim Firstrow As Integer
Dim Lastrow As Integer
Dim totscore1 As Integer
Dim i As Integer, j As Integer
Sheets(“sheet1”).Select
Range(“D2”).Select
Firstrow = ActiveCell.row
Selection.End(xlDown).Select
Lastrow = ActiveCell.row

totscore1 = Lastrow – Firstrow
Sheets(“sheet1”).Select
Range(“B2”).Select
Firstrow = ActiveCell.row
Selection.End(xlDown).Select
Lastrow = ActiveCell.row

Range(“F2”).Select
For i = 0 To totscore1
val = Cells(2, 3).Value – Cells(2, 4).Value
ActiveCell.Value = val
ActiveCell.Offset(1, 0).Select
Next i
End Sub

Hi,
The code is working only for the cells D2-B2. And the same value getting printed. I want the results down the column (Relative referencing).. D3-B3, D4-B4… so on. Please help

Hi, I want to add data from cells to an inputbox, do you have any clue on how to do that?
thanks in advance!

Rutger

Ok. So this problem is getting on my nerves for some time now. I made damage calculator using macros. Works pretty good. Now I want to add temporary hit points into account.
Let’s say it looks like this:

DMG TotalDMG TempHP HP
2 3 7 24

So, basicaly, I need to deplete TempHP first and with every dmg calculation to see if there is any TempHP left. So with every calculation I need to check the value of TempHP cell and DMG cell, then do TempHP – DMG (7-2) and the result should be new TempHP value (5). And when DMG is bigger than TempHP do the following: HP – (DMG – TempHP) and set TempHP to 0.

Thanks in advance.

Hi,

I want to select the data and month from the Date in cell(a1)(containing formula) and file 2016-06-07 ABCD should be opened using that date and month.

Thanks

Dear Sir,

I have some ComboBox – 8No’s and TextBox -40No’s in Sheet1 and our employee details (Emp.ID, Name, Date of Birth, Email, Contact Numbers) in Sheet2. I need to display employee id in Combobox 1 to 8 and when i select anyone employee id in ComboBox then his all details wants to display other Testboxes in Sheet1 as per below. But (Sr.No) not in Sheet2 database. Anyway i need to display when i select second Combobox, then Sr.No wants to display number-2 automatically. Kindly anyone give a solution to solve my query.

Sr.No Emp. ID Name Date of Birth Email Contact No.
1 1201 AAAAAAAA1 12/12/1980 dfs 1111111111
2 1202 AAAAAAAA2 12/13/1980 dfs 1111111112
3 1203 AAAAAAAA3 12/14/1980 dfs 1111111113
4 1204 AAAAAAAA4 12/15/1980 dfs 1111111114
5 1205 AAAAAAAA5 12/16/1980 dfs 1111111115
6 1206 AAAAAAAA6 12/17/1980 dfs 1111111116
7 1207 AAAAAAAA7 12/18/1980 dfs 1111111117
8 1208 AAAAAAAA8 12/19/1980 dfs 1111111118

Hi, this is wonderful platform and thank you all for yours comments! I’m pretty new in VBA and I’m finding this very helpful. Supposing I have excel file that has huge informations capture in it and these informations could be actions, time the action was done, number of actions performed for several users and are all ranged columns per user. How should write a script to extract this data based on those that user performed with <3 minutes, compute totals number of actions done by all user in specific time slot, categories the number of activities done in range of <2,3-4, 4< time difference. I will be very delighted.

Dear All how can I get the value in spread sheet (for example day and month) and use it as reference value to plot the point in VBA?
Can you please explain it??

Hi,
I am trying a scenario in which I have a Mini Invoice and my requirement is to save the data of the invoice and need to retrieve the saved data based on Invoice Number. I request to help me in this scenario giving the desired code.
Thanks in advance.

Can any one show me how to write a macro which reads values from a sheet with two columns consisting of dates and numerical values and calculates slope from these values but writes value of slope and date when slope changed from positive to negative and visa versa and ignore all values and not write only the date and slope at slope change only.
Your input is appreciated.
Naem

I have name of company in Column one with entries Employees name, salary, phone number in next column.
The name of the company in first column repeats.

I dont want the name of the company again and again. Only other entries like Employees Name, salary and phone number in new sheet.
The Company name should appear once, rest all cells for same company must remain empty.

Please suggest.

Dim strCycle As String, i As Integer, trgSheet As String, sht As Worksheet
With Sheets(“Run”)
i = 2
For Each sht In Sheets
If sht.Name “Run” Then
sht.Delete
End If
Next

Do While True
strCycle = .Cells(i, 1)
If Len(Trim(strCycle)) = 0 Then Exit Do
If strCycle = “All” Then
trgSheet = “All_Summary”
LoadDetail srcJIRAFile, “select LEFT([Test Summary],INSTR([Test Summary],’_’)-1) as EPIC_ID, x.* ” & _
“from [Sheet1$] x WHERE Step=’Step1′”, trgSheet
Else
trgSheet = strCycle & “_Summary”
LoadDetail srcJIRAFile, “select LEFT([Test Summary],INSTR([Test Summary],’_’)-1) as EPIC_ID, x.* ” & _
“from [Sheet1$] x WHERE Step=’Step1′ and CycleName in (‘” & strCycle & “‘)”, trgSheet
End If
ThisWorkbook.Save

LoadDetail ThisWorkbook.FullName, “select x.* from [” & trgSheet & “$] x,(SELECT [Test Summary], MAX(date+time) as dt” & _
” FROM [” & trgSheet & “$] group by [Test Summary]) t where ” & _
” x.[Test Summary]=t.[Test Summary] and (x.date+x.time)=t.dt”, strCycle & “_Snap”

If strCycle “All” Then

LoadDetail ThisWorkbook.FullName, “TRANSFORM COUNT(*) select EPIC_ID ” & _
“FROM (Select Status,EPIC_ID from [” & strCycle & “_Snap$] UNION ALL ” & _
“Select ‘Total’ as Status,EPIC_ID from [” & strCycle & “_Snap$]) group by EPIC_ID PIVOT Status”, strCycle & “_Status”
End If
If strCycle = “All” Then
LoadDetail ThisWorkbook.FullName, “TRANSFORM COUNT(*) select CycleName ” & _
“FROM (Select Status,CycleName from [” & strCycle & “_Snap$] UNION ALL ” & _
“Select ‘Total’ as Status,CycleName from [” & strCycle & “_Snap$]) group by CycleName PIVOT Status”, strCycle & “_Status”
End If

i = i + 1
Loop

End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Hi – I am totally new to excel macro. I give below my macro. i would like to write the macro in one file and execute the same many times on the same day based on some criteria.

My data has City name, date of transaction and other data spread over a few columns .

1. I would like to select one file where the all transaction details are available. (Say AllData.xlsx).
2. City Name will be received as Input at Macro as variable

3. Need to select and open this file and read the contents row by row and write to another file -(Output File) (this file name to be generated into a variable like – OutputFileName = City Name + “Transactions Till ” + Yesterday Date + “.xlsx”. This file need to be created inside macro with the name assigned.

4. The data read in AllData should be one row in full (or column by column as you recommend) and based on the first column (city name) matching with City Name received as input.

5. If matching need to write on Output file one entire row.
6. Read Next Row from AllData and if matching write to Output file
7. Above process to be repeated till Last Record of AllData File.

I tried but not getting correct code. Request you to give me the code and help me asap.

Kumar S