Posts by naira

Perhaps someone could take a look at this thread and see if it can be used for a working solution: https://stackoverflow.com/ques…ecific-window-coordinates

Hi!

This is a follow-up thread from the thread to Open Chrome from VBA posted at Google Chrome VBA

I am using following code to open 2 urls in 2 tabs in first chrome window and 1 url in another chrome window:

Public Sub ChromeNewWindows()
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe --new-window -url https://www.facebook.com")
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url https://www.facebook.com")
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe --new-window -url https://www.google.com")
End Sub

The Chrome windows open nicely in maximized mode.

However, I need to open both windows in specific window sizes and at specific locations on the screen, say:

.Top = 100, .Left = 100 .Height = 100 .Width = 100 for window 1

.Top = 100, .Left = 300 .Height = 150 .Width = 150 for window 2

I have tried below code to resize and re-position the windows but it doesn't work. It seems that I probably need to open/ change Chrome window state to Normal state and then below code might work

Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe  --new-window -url http://www.google.com/ --chrome-frame --window-size=300,200 --window-position=80,40")

Alternately, I use the below code to first maximize my excel window, get the screen size of the current monitor, change the excel application window state to Normal, and then resize and reposition my excel window based on my screen size. But I am unable to use the With Application command for the chrome window:

Dim FullWidth As Integer, FullHeight As Integer
With Application
    .WindowState = xlMaximized
    FullWidth = .Width
    FullHeight = .Height
    .WindowState = xlNormal
    .Top = FullHeight - 100
    .Left = FullWidth - 100
End With

Would appreciate if someone could recommend a working solution since my Shell skills are quite rusty.

Note: Sample excel file attached for testing.

Thanks.

Hi!

Following Macro:

1. Exports range from worksheet "Names" (sheet that may not be the currently active sheet) to txt file with comma separated values in text file.
2. The export of range starts from Row 2 and consists of data till last row for columns F & G.

However, I now need export data from column F to column AV viz. 43 columns, and modifying the macro to manually write all the columns in the macro would be time consuming (and probably slow down execution too).

I was wondering if the macro can be modified to export data in all 43 columns with only changing a few lines of code.If not, perhaps someone could help me with fresh code for the above requirements.
I would be OK with the data being separated with commas or with tabs, since I need to re-import the data to another excel file.

[VBA]Sub ExportData()
On Error Resume Next

Dim FilePath As String
Dim LastRow As Long
Dim CellData As String
Dim CellData1 As String
Dim WS2 As Worksheet
Dim i As Long
Dim RowsNos As Long

Set WS2 = Workbooks("Book1.xlsm").Sheets("Names")
FilePath = "C:\Data" & "\" & "Data.txt"
RowsNos = Workbooks("Book1.xlsm").Sheets("Names").Range("F" & Rows.Count).End(xlUp).Row
Open FilePath For Output As #1
For i = 2 To RowsNos
CellData = WS2.Cells(i, 6).Value '<< Col F is being exported
CellData1 = WS2.Cells(i, 7).Value '<< Col G is being exported
Print #1, CellData & "," & CellData1
Next i
Close #1
End Sub[/VBA]

Note: Sample file attached.

Hi!

As per standard excel the length of databar is equal to the value in the cell where the databar is displayed (subject to upper and lower limits specified in other cells).
Following macro allows me to create a Databar using min. and max range between cells A3 to A4, and the databar is displayed in cells A2 to A4.

Sub databars()
Dim rg As Range
Dim db As Databar


Set rg = Range("A2", Range("A3").Range("A4"))
Set db = rg.FormatConditions.AddDatabar
        With db
              .BarColor.Color = vbGreen
              .BarFillType = xlDataBarFillGradient
        End With
End Sub

I was wondering whether it could be possible to modify the macro to display the databar in Cell A1, with the databar length corresponding to value in cell A2 and min/ max range being held in cells A3 to A4.

For example, if the Value in cell A2 is 300, and range in cells A3-A4 is 1-900, the length of databar being displayed in cell A1 should correspond to 300, even though the value in cell A1 is 600. (Perhaps the databar could read the cell value from a range in the macro itself and display the databar in the worksheet)

[IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.excelforum.com\/attachments\/excel-programming-vba-macros\/607227d1548062703-databar-based-on-another-cell-value-using-vba-macro-capture.png"}[/IMG2]

Also, the databars are currently being displayed in cell A2 to A4. I believe this is because I have not defined rg As Range properly. Can someone please help to let the databar appear only in cell A2.

Sample file attached.

Note: This is a duplicate post from another forum. No reply received there, so request posted on this forum

https://www.excelforum.com/excel-programming-vba-macros/1260963-databar-based-on-another-cell-value-using-vba-macro.html#post5048133

Hi!

As per Standard Excel the length of databar is equal to the value in the cell where the databar is displayed (subject to upper and lower limits specified in other cells).
Following macro allows me to create a Databar using min. and max range between cells A3 to A4, and the databar is displayed in cells A2 to A4.

Sub databars() Dim rg As Range Dim db As Databar  Set rg = Range("A2", Range("A3").Range("A4")) Set db = rg.FormatConditions.AddDatabar  With db    .BarColor.Color = vbGreen    .BarFillType = xlDataBarFillGradient     End With  End Sub

I was wondering whether it could be possible to modify the macro to display the databar in Cell A1, with the databar length corresponding to value in cell A2 and min/ max range being held in cells A3 to A4. For example, if the Value in cell A2 is 300, and range in cells A3-A4 is 1-900, the length of databar being displayed in cell A1 should correspond to 300, even though the value in cell A1 is 600. (Perhaps the databar could read the cell value from a range in the macro itself and display the databar in the worksheet)
[Blocked Image: https://www.excelforum.com/attachments/excel-programming-vba-macros/607227d1548062703-databar-based-on-another-cell-value-using-vba-macro-capture.png]

Also, the databars are currently being displayed in cell A2 to A4. I believe this is because I have not defined rg As Range properly. Can someone please help to let the databar appear only in cell A2.

Sample file attached.
Note: This is a duplicate post from another forum

https://www.excelforum.com/excel-programming-vba-macros/1260963-databar-based-on-another-cell-value-using-vba-macro.html#post5048133

No reply received there, so request posted on this forum

Re: Formula to find cells with vlaues between 95s and 100s and 100s and 105s

Hi Batman!
Your solution works as required.
True/ False is ok with me, I can pick up from there.
Thanks for the help.

Naira

Hi!

I need 2 formulae, one to find cells with values between 95s and 100s such as between 195 and 200, 295 and 300, 1195 and 1200 etc. and scond to find cells with values between 100s and 105s such as between 200 to 205, 1200 to 1205 etc.

Please note that the cell values will be in 2 decimal places, such as 195.94, 1195.30, 1205.30 etc.

The solution may please be checked with values such as 430.95, 150 etc. since I tried dividing the cell values by 100 and rounding off to zero decimal, but then excel treats values such as .30 as .3 and I was getting false results.

Thanks,

Naira

Hi!

I was wondering if it is possible to change the value of a cell based on the currently active worksheet of another workbook?

I have 2 workbooks, Book1.xlsm (macro enabled workbook) and Book2.xlsx (normal workbook), opened simultaneously.

If the currently active worksheet in Book2.xlsx is Sheet1, the value of Cell A1 in Sheet1 of Book1.xlsm should be 1.
If the currently active worksheet in Book2.xlsx is Sheet2, the value of Cell A1 in Sheet1 of Book1.xlsm should be 2.

Finally, I want to run a macro based on whenever this cell value changes. One could run it based on Current Active Sheet Cell value change, but the problem is that the cell where the value is changing is not on the currently active sheet.

Thanks,

Naira.

Re: Macro to delete everything after occurence of second comma in a Range

Hi Guys,

Thanks a lot for your replies.

1. S O's formula works great
2. KjBox's solution gives me a subscript out of range error
3. jindon's solution works great too

Thread closed.

Hi!

I have data in multiple rows and columns that looks as below, i.e., group of nos. and ratios separated by commas:

[TABLE="width: 578"]

[TD="align: center"]USA
[/TD]
[TD="align: center"]CANADA
[/TD]
[TD="align: center"]RUSSIA
[/TD]

39008,29396,1.327 - 1,

10056,17020,1 - 1.693,95

28829,43751,1 - 1.518,89

4380,6085,1 - 1.389,

25805,24920,1.036 - 1,

5259,9780,1 - 1.860,

5771,7304,1 - 1.266,

30066,30074,1 - 1.000,20

13026,17561,1 - 1.348,

6271,6129,1.023 - 1,

29012,33676,1 - 1.161,27

10829,16963,1 - 1.566,

6268,6129,1.023 - 1,84

29071,34456,1 - 1.185,40

16569,22161,1 - 1.337,

6268,6229,1.006 - 1,83

28213,34609,1 - 1.227,45

13003,19053,1 - 1.465,

6463,6223,1.039 - 1,55

28800,33519,1 - 1.164,44

12693,20713,1 - 1.632,

6464,6836,1 - 1.058,

28961,34922,1 - 1.206,46

15448,22052,1 - 1.427,62

7064,7646,1 - 1.082,

30333,37335,1 - 1.231,53

20819,28523,1 - 1.370,63

6995,8277,1 - 1.183,

15386,37116,1 - 2.412,88

20894,30302,1 - 1.450,67

6995,8457,1 - 1.209,

15674,35386,1 - 2.258,89

21594,29997,1 - 1.389,63

7201,8460,1 - 1.175,

15542,38347,1 - 2.467,95

21161,30614,1 - 1.447,69

[/TABLE]

What I need is a macro (or a formula) that deletes everything after the occurrence of the second comma in each cell, including the second comma also.

So my new data should look like this:

[TABLE="width: 578"]

USA

CANADA

RUSSIA

39008,29396

10056,17020

28829,43751

4380,6085

25805,24920

5259,9780

5771,7304

30066,30074

13026,17561

6271,6129

29012,33676

10829,16963

6268,6129

29071,34456

16569,22161

6268,6229

28213,34609

13003,19053

6463,6223

28800,33519

12693,20713

6464,6836

28961,34922

15448,22052

7064,7646

30333,37335

20819,28523

6995,8277

15386,37116

20894,30302

6995,8457

15674,35386

21594,29997

7201,8460

15542,38347

21161,30614

[/TABLE]

Please note that the macro will need to be run on a range of about 275 columns and 1500 rows.
If the solution is a formula, I can always use it on another sheet by looking at the data range on the first sheet.

Thanx,

Naira

Re: Macro not picking up row no. from Worksheet cell for copy paste of dynamic range

Ah!
The missing dot problem surfaces again. Was struggling with it just last week here: http://www.ozgrid.com/forum/sh…92247&p=733538#post733538

Thanks for your reply StephenR (works great)

Hi,

Following macro copies data from a row of a range every 05 seconds and pastes it to another range in the next empty row (in top to down sequence).
The row no. from which the data is to be copied is picked up from cell L1 and the row no. increases dynamically with a simple count function on the spreadsheet.

The macro works fine when Sheet1 (on which the copy - paste functions are supposed to run) is the currently the active sheet. However, if the currently active sheet or workbook is different from Book1.xlsm - Sheet1, then the macro starts copying data from Row1 of the range (ignoring the row no. from cell L1).

Need help to modify the macro to pick up the row no. from cell L1 of Book1.xlsm-Sheet1 even if the currently active workbook or worksheet is different.

Option Explicit
Dim TimeToRun As Date
Dim Test1 As Range


Sub Home_Test()
    With Workbooks("Book1.xlsm").Sheets("Sheet1")
           Set Test1 = .Range("M" & Range("L1").Value & ":" & "V" & Range("L1").Value)


        Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 10).Value = Test1.Value
        TimeToRun = Now + TimeValue("00:00:05")
        Application.OnTime TimeToRun, "Home_Test"
 
    End With
End Sub

Note: Working sample file attached. It can be tested by starting the macro and keeping another worksheet active or opening another worksheet in a new window and keeping that active.

Re: Fine tune a macro that replaces -ve values with 1 in a Range

HOLYCOW....
If only all the problems of life could be resolved with a simple missing dot.
(Thanx... solution works great).

Re: Fine tune a macro that replaces -ve values with 1 in a Range

Quote from ashu1990;733455

naira ur missing to give the refrence for the file you want to run....
the code which you provided will only work on Book1.

Thank for the reply, tested it but your solution doesn't work. It is EXACTLY OPPOSITE to what I want.
The code needs to run only on Sheet2 of Book1.xlsm NOT on the currently active worksheet.
In other words, as already pointed out in my original post, I want it to run only on Sheet2 of Book1.xlsm, no matter what is the currently active workbook or worksheet.

Still waiting for a solution...

Hi!

Following macro replaces negative values in the range A2:I30 with 1.

Sub replace()
Dim rng As Range
Application.ScreenUpdating = False
    With Workbooks("Book1.xlsm").Sheets("Sheet2")
    For Each rng In Range("A2:I30")
        If rng.Value < 0 Then
            rng = 1
        End If
    Next rng
    End With
Application.ScreenUpdating = True
End Sub

The macro runs fine when the current active worksheet is same as worksheet Sheet2. However, if the current active worksheet OR workbook is different from Book1.xlsm or Sheet2, the macro either throws error (if run in background through another macro OR simply does not run if run by a button on another Worksheet).

Can someone please point out what am I missing here.

Note: Sample file attached.

Thanks
Naira

Re: Formula to find intermediate Max value in a column

Thanks NBVC,

Your solution works as per my request.

Hi!

I have a set of numbers in a column in a random order from cell A4 to cell A29.

What I need is a formula to identify the lowest value in the range (already got it with =min(A4:A29) and the highest value from this lowest value to the bottom of the column.

For example, in the below set of data, the lowest value is 2 and the highest value between 2 to the bottom of the column is 45 (even though the max of the range is 48, since it is above 2, it is being ignored).

Data
A
B
32
11
22
33
48 (this value will be ignored since it is above the min. of the range)
14
22
2
19
37
25
27
19
29
10
30
21
24
11
17
3
29
24
45
15
10

2 (Min)
45 (Max)

Note:

1. Not looking for a macro since the data is dynamic and formula will be used for 250 columns. Would appreciate as simple a formula/s as possible (even if some helper cells are to be used). Also, I have found that array formulas tend to slow down calculations, so I would like to avoid those too).

2. Fresh data is added at the top of the column and old data is gradually pushed down, so that there will be some blank cells below the last cell that contains data. Therefore the formula needs to address blank cells as well;

3. There is some numeric data below cell A38. Therefore the formula needs to be restricted to range A4 to A29. Results will be held in Cells A34 (minimum) and Cell A35 (maximum).

4. Sample file attached.

Thanx,
Naira

Re: fine tune macro to copy data from closed workbook

Works great!!!
Thanx a lot Batman
You saved Gotham again...

Hi!

I have the below macro to copy data from a closed workbook at path C:\tmp\x.xlsx to the currently open workbook:

Sub GetDataFromClosedBook()
 Dim mydata As String
 mydata = "='C:\tmp\[x.xlsx]Sheet1'!$a$1:$g$2501"  'data location & range to copy
 With ThisWorkbook.Worksheets("chk").Range("a1:g2501") 'link to currently open workbook/ worksheet
 .Formula = mydata
 .Value = .Value 'convert formula to text
 .WrapText = False
 End With
 End Sub

The code works perfectly if I was copying the data into the range A1:G2501 of the currently open workbook.
However, if I wish to copy the data to some other range such as C1:I2501 of the currently open workbook (with source data range being A1:G2501), col. A & B remain blank, columns C to G get the same data as in col. C to G of the source workbook while I get #Value in Col. H & I.

Can some one point out what am I missing here.
PS: Please note that the source workbook should not be opened while copying data since it will slow down the process a lot.

Thanx,

Naira

Re: Modify Macro to copy formula to new range and update cell references

Bump!

Can someone please review the problem in the solution given by S O and advise how to rectify the issue being faced by me?

Thanx