Re: $$Excel-Macros$$ Regarding VLOOKUP FUNCTION IN CLOSEST MATCH

[Anoop K Dixit]

Hi Sanjib,

Please find attachment.


Regards,
Anoop


On Fri, Feb 3, 2012 at 1:28 PM, Sanjib Chatterjee <
chatterjee.kolk...@gmail.com> wrote:

>
> Dear Experts,
>
> Please See the attachment and Help me regarding VLOOKUP FUNCTION
>
> Thanking you in Advance
>
> Sanjib
>
>
>
>
>
>
>
>
>
> --
> -
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Regards,
Anoop

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


VLOOKUP_FUNCTION.xls
Description: MS-Excel spreadsheet

RE: $$Excel-Macros$$ paste special problem

[danial mansoor]

Option Explicit
Sub dovalues()
'Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
 ws.Cells.Copy

ws.Cells.PasteSpecial (xlPasteValues)

Next ws

'Range("a1").Select
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
 




From: dguille...@gmail.com
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ paste special problem
Date: Fri, 3 Feb 2012 15:18:36 -0600









Show me.
 
 
Don 
Guillett
SalesAid Software
dguille...@gmail.com


 

From: danial mansoor 
Sent: Friday, February 03, 2012 3:07 PM
To: excel-macros@googlegroups.com 

Subject: RE: $$Excel-Macros$$ paste special 
problem
 


thanx,your work fine with with little changes.







From: dguille...@gmail.com
To: excel-macros@googlegroups.com
Subject: Re: 
$$Excel-Macros$$ paste special problem
Date: Fri, 3 Feb 2012 14:33:36 
-0600







Run mine
 
 
Don 
Guillett
SalesAid Software
dguille...@gmail.com


 

From: danial mansoor 
Sent: Friday, February 03, 2012 2:09 PM
To: excel-macros@googlegroups.com 

Subject: RE: $$Excel-Macros$$ paste special 
problem
 

in this i have t select each sheet is there a way i run macro and 
every sheet get updated with values and no formula shown?bt plz consider huge 
file








Date: Fri, 3 Feb 2012 23:47:03 +0530
Subject: Re: $$Excel-Macros$$ paste 
special problem
From: vijayajith...@gmail.com
To: 
excel-macros@googlegroups.com

Hi mansoor, 
 
sub tex()
selection.value = selection.value
end sub



On Fri, Feb 3, 2012 at 9:08 PM, danial mansoor  
wrote:


  
  i have a big file with many sheets i want to convert formula in 
  values,if i do individually it will take lotes of time can any one tell me 
the 
  macro which will work?

Note:macro should work fine in huge file i have 
  a macro which is working fine with small file but not in huge file urgent 
help 
  will be appreciated.




  


  
  
  
  Date: Thu, 2 Feb 2012 09:21:22 +0530
Subject: Re: $$Excel-Macros$$ 
  $$Excel-Ma​cros$$ Index & Match Formula
From: vijayendrar...@gmail.com
To: excel-macros@googlegroups.com


  Hi Sam,
   
  Your formulas resolves my problem and thanks a lot for the same. Can you 
  please let me know the logic behind of this formula.
   
  Rohan,
  I wanted to pick the employees name from the same project and Sam done it 
  in right way.
   
  Regards,
  Vijayendra


  2012/2/1 Sam Mathai Chacko 

  Here's the result.

I have also 
sorted the list based on project. Please confirm if that is what you are 
looking for.

Regards,
Sam Mathai Chacko 





On Wed, Feb 1, 2012 at 8:53 PM, Vijayendra Rao  
wrote:


  Dear All,
   
  I want to put index & match formula (or any other formula) in 
  attached file, can you please help me to put the formula.
   
  Excel file is attached along with email.
   
  Regards,
Vijayendra
94491 67631
-- 
FORUM RULES 
  (986+ members already BANNED for violation)
 
1) Use concise, 
  accurate thread titles. Poor thread titles, like Please Help, Urgent, 
Need 
  Help, Formula Problem, Code Problem, and Need Advice will not get quick 
  attention or may not be answered.
 
2) Don't post a question in 
  the thread of another member.
 
3) Don't post questions 
  regarding breaking or bypassing any security measure.
 
4) 
  Acknowledge the responses you receive, good or bad.
 
5) 
  Cross-promotion of, or links to, forums competitive to this forum in 
  signatures are prohibited. 
 
NOTE : Don't ever post personal 
  or confidential data in a workbook. Forum owners and members are not 
  responsible for any 
  loss.
 
--
To 
  post to this group, send email to excel-macros@googlegroups.com


-- 
Sam Mathai 
Chacko 




-- 
FORUM RULES (986+ members already BANNED for 
violation)
 
1) Use concise, accurate thread titles. Poor thread 
titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, 
and Need Advice will not get quick attention or may not be 
answered.
 
2) Don't post a question in the thread of another 
member.
 
3) Don't post questions regarding breaking or bypassing 
any security measure.
 
4) Acknowledge the responses you receive, 
good or bad.
 
5) Cross-promotion of, or links to, forums 
competitive to this forum in signatures are prohibited. 
 
NOTE : 
Don't ever post personal or confidential data in a workbook. Forum owners 
and members are not responsible for any 
loss.
 
--
To 
post to this group, send email to excel-macros@googlegroups.com


-- 

  ಧನ್ಯವಾದಗಳು,
  ವಿಜಯೇಂದ್ರ,
  ೯೪೪೯೧ ೬೭೬೩೧
   
  Regards,
Vijayendra
94491 67631

-- 
FORUM RULES 
  (986+ members already BANNED for violation)
 
1) Use concise, 
  accurate t

Re: $$Excel-Macros$$ Urgent Help

[Lakshman Prasad]

Dear Pravesh,
 
You can use lookup too
 
VLOOKUP(I13:I19,D11:E24,2)
 
 


From: PRAVESH KUMAR 
To: excel-macros@googlegroups.com 
Sent: Friday, 3 February 2012 11:06 PM
Subject: $$Excel-Macros$$ Urgent Help

Hi,


please help ASAP.

please find attached sheet.


Thanks

Pravesh Kumar

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Re: Currency Calculator to calculate the cells values. (But it should not disturb the programmed cells).

[Kris]

Hi

Please find attached.

Kris

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Conversion.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: $$Excel-Macros$$ paste special problem

[dguillett1]

Please tell me WHY what I send did NOT work as desired. It was TESTED. 
It is ONLY looking at cells with formulas instead of ALL CELLS and does NOT 
need to copy/paste. 
Your procedure could take a LONG time to execute.

Sub dovalues()
dim ws as worksheet
For Each ws In ActiveWorkbook.Sheets
With ws.Cells.SpecialCells(xlCellTypeFormulas)
.Value = .Value
End With
Next ws
End Sub


Don Guillett
SalesAid Software
dguille...@gmail.com

From: danial mansoor 
Sent: Saturday, February 04, 2012 5:01 AM
To: excel-macros@googlegroups.com 
Subject: RE: $$Excel-Macros$$ paste special problem

Option Explicit
Sub dovalues()
'Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Cells.Copy

ws.Cells.PasteSpecial (xlPasteValues)

Next ws

'Range("a1").Select
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
 








From: dguille...@gmail.com
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ paste special problem
Date: Fri, 3 Feb 2012 15:18:36 -0600


Show me.


Don Guillett
SalesAid Software
dguille...@gmail.com

From: danial mansoor 
Sent: Friday, February 03, 2012 3:07 PM
To: excel-macros@googlegroups.com 
Subject: RE: $$Excel-Macros$$ paste special problem


thanx,your work fine with with little changes.






From: dguille...@gmail.com
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ paste special problem
Date: Fri, 3 Feb 2012 14:33:36 -0600


Run mine


Don Guillett
SalesAid Software
dguille...@gmail.com

From: danial mansoor 
Sent: Friday, February 03, 2012 2:09 PM
To: excel-macros@googlegroups.com 
Subject: RE: $$Excel-Macros$$ paste special problem

in this i have t select each sheet is there a way i run macro and every sheet 
get updated with values and no formula shown?bt plz consider huge file







Date: Fri, 3 Feb 2012 23:47:03 +0530
Subject: Re: $$Excel-Macros$$ paste special problem
From: vijayajith...@gmail.com
To: excel-macros@googlegroups.com

Hi mansoor, 

sub tex()
selection.value = selection.value
end sub



On Fri, Feb 3, 2012 at 9:08 PM, danial mansoor  wrote:

  i have a big file with many sheets i want to convert formula in values,if i 
do individually it will take lotes of time can any one tell me the macro which 
will work?

  Note:macro should work fine in huge file i have a macro which is working fine 
with small file but not in huge file urgent help will be appreciated.








--
  Date: Thu, 2 Feb 2012 09:21:22 +0530
  Subject: Re: $$Excel-Macros$$ $$Excel-Ma​cros$$ Index & Match Formula
  From: vijayendrar...@gmail.com
  To: excel-macros@googlegroups.com


  Hi Sam,

  Your formulas resolves my problem and thanks a lot for the same. Can you 
please let me know the logic behind of this formula.

  Rohan,
  I wanted to pick the employees name from the same project and Sam done it in 
right way.

  Regards,
  Vijayendra


  2012/2/1 Sam Mathai Chacko 

Here's the result.

I have also sorted the list based on project. Please confirm if that is 
what you are looking for.

Regards,
Sam Mathai Chacko 



On Wed, Feb 1, 2012 at 8:53 PM, Vijayendra Rao  
wrote:

  Dear All,

  I want to put index & match formula (or any other formula) in attached 
file, can you please help me to put the formula.

  Excel file is attached along with email.

  Regards,
  Vijayendra
  94491 67631

  -- 
  FORUM RULES (986+ members already BANNED for violation)
   
  1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
   
  2) Don't post a question in the thread of another member.
   
  3) Don't post questions regarding breaking or bypassing any security 
measure.
   
  4) Acknowledge the responses you receive, good or bad.
   
  5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
   
  NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
   
  
--
  To post to this group, send email to excel-macros@googlegroups.com





-- 
Sam Mathai Chacko 


-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
 
2) D

Re: $$Excel-Macros$$ paste special problem

[dguillett1]

Original Post
“i have a big file with many sheets i want to convert formula in values,if i do 
individually it will take lotes of time can any one tell me the macro which 
will work?”

Says nothing about the type formula so my code looks at ONLY cells that contain 
ANY and ALL formulas in the sheet.
No mention of Chart sheets but I should have included an “on error resume next” 
or for each WORKsheet
No mention of MULTIPLE files so I look in the Active. I adhere to the KISS 
principle “Keep it Simple Stupid”

option explicit
Sub dovalues()
dim ws as worksheet
on error resume next ‘ or for each worksheet
For Each ws In ActiveWorkbook.Sheets
With ws.Cells.SpecialCells(xlCellTypeFormulas)
  .Value = .Value
End With
Next ws
end Sub


Don Guillett
SalesAid Software
dguille...@gmail.com

From: Asa Rossoff 
Sent: Friday, February 03, 2012 8:36 PM
To: excel-macros@googlegroups.com 
Subject: RE: $$Excel-Macros$$ paste special problem

Hi Danial,

If you are not done with your task, you may want to consider this variation on 
Don's approach.   It's not thoroughly tested, but some of the changes should 
improve reliability.

 

The principle differences:

(1) Processes Areas (distinct ranges) containing formulas in each worksheet one 
at a time, rather than all at once.  Excel can corrupt your data (change it) in 
some cases without this change;

(2) Uses the Value2 property of formula ranges instead of the Value property.  
The Value property rounds off currency and date/time values to VBA Date and VBA 
Currency datatype limits, changing your underlying data.  Value2 is also faster;

(3) Only processes sheets in the Worksheets collection, skipping chart and 
legacy macro sheets;

(4) Loops through all open workbooks, prompting to process them (skipping 
hidden workbooks, but processing hidden sheets in workbooks with a visible 
window);

(5) If errors occur during processing you have the opportunity to continue 
processing anyway.

 

Sub ConvertToValues()

Dim wb As Workbook, ws As Worksheet, a As Range

Dim e As String, abort As Boolean

Const ProcTitle = "Convert to Values"

On Error Resume Next

Debug.Print vbCrLf & ProcTitle & " Start."

For Each wb In Application.Workbooks

If wb.Windows(1).Visible Then ' quick check to skip hidden workbooks 
(personal.xls*, addins,...)

If MsgBox("Process " & wb.Name & "?", _

  vbQuestion + vbYesNo + vbDefaultButton2, _

  ProcTitle) = vbYes Then

Debug.Print "Processing " & wb.FullName

abort = False

For Each ws In wb.Worksheets

Debug.Print , ws.Name

For Each a In 
ws.Cells.SpecialCells(xlCellTypeFormulas).Areas

ConvertValues:

If Not a Is Nothing Then

Debug.Print , , a.AddressLocal & " (" & 
a.Cells.Count & " cells)"

a.Value2 = a.Value2

If Err.Number <> 0 Then

e = "ERROR " & Err.Number & " - " & 
Err.Description

Err.Clear

Debug.Print e

Select Case _

  MsgBox(e & vbCrLf & vbCrLf & _

  "For processing of this workbook...", _

  vbQuestion + vbAbortRetryIgnore + 
vbDefaultButton3, _

  ProcTitle & " - [" & wb.Name & "]" & ws.Name 
& "!" & a.AddressLocal)

Case vbRetry

Debug.Print "User retry."

GoTo ConvertValues

Case vbAbort

Debug.Print "User abort."

abort = True

Exit For

End Select

End If

End If

Next a

If abort Then Exit For

Next ws

Else

Debug.Print "User skipped " & wb.FullName

End If

Else

Debug.Print "Skipping hidden workbook " & wb.FullName

End If

   Next wb

Debug.Print vbCrLf & ProcTitle & " Finish."

Cleanup:

Set a = Nothing

Set ws = Nothing

Set wb = Nothing

End Sub

 

Asa

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of dguillett1
Sent: Friday, February 03, 2012 8:12 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ paste special problem

 

Probably the most efficient to do only those with formulas

 

option explicit

Sub dovalues()

dim ws as worksheet

For Each ws In ActiveWorkbook.Sheets

With ws.Cells.SpecialCells(xlCellTypeFormulas)

  .value = .Value

End With

Next ws

End Sub

 

Don Guillett
SalesAid Sof

$$Excel-Macros$$ Text Box - Properties

[Cab Boose]

Hi

I have a userform open with a 'text box' .  Want to link with a cell in
sheet.

However the 'text box'  properties does not show a 'linked cell'
heading. I am sure it use to.  How do I restore the missing property or
is there another text box I should use.

Thks

Charlie

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Text Box - Properties

[Asa Rossoff]

The Active X Text Box control can be linked to a cell, but I think it is
only available for use on a worksheet, not a userform. and everyone keeps
telling me to avoid Active X controls on worksheets like the plague, anyway.

 

To duplicate the linking behavior, you'll have to use a little VBA code.
For example, in your form code you could use:

Public TextBox1Cell As Range

 

Private Sub UserForm_Initialize()

Set TextBox1Cell = ActiveWorkbook.Sheets("Sheet1").Range("A1")

End Sub

 

Private Sub UserForm_Terminate()

Set TextBox1Cell = Nothing

End Sub

 

Private Sub UserForm_Activate()

TextBox1.Value = TextBox1Cell.Value2

End Sub

 

Private Sub TextBox1_Change()

TextBox1Cell.Value2 = TextBox1.Value

End Sub

If you also need the control on the form to update if the worksheet cell is
changed for some other reason while the form is still open (a calculation,
other code,.) then you can add code to your worksheet's Calculate and Change
events.  If the cell will only contain a value and not a formula, you don't
need the calculate event.

Private Sub Worksheet_Change(ByVal Target As Range)

With UserForm1

If .Visible Then

If Not Application.Intersect(Target, .TextBox1Cell) Is Nothing
Then

.TextBox1.Value = .TextBox1Cell.Value2

End If

End If

End With

End Sub

 

Private Sub Worksheet_Calculate()

With UserForm1

If .Visible Then

If .TextBox1.Value <> .TextBox1Cell.Value2 Then

.TextBox1.Value = .TextBox1Cell.Value2

End If

End If

End With

End Sub

 

Asa

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Cab Boose
Sent: Saturday, February 04, 2012 4:27 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Text Box - Properties

 

Hi

 

I have a userform open with a 'text box' .  Want to link with a cell in
sheet.

 

However the 'text box'  properties does not show a 'linked cell' heading.
I am sure it use to.  How do I restore the missing property or is there
another text box I should use.

 

Thks

 

Charlie

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Need a small MACRO to SPLIT

[Somnath Khadilkar]

Good Morning.
I receive  XL file comprising 1-sheet. Depending upon Colm 'A' I
need to SPLIT this single sheet into MULTIPLE sheets [ with this colm
as it name. ] The Distinct entries in this colm. would be less than
say 25, but the no of entries cud be anything say 10 to 100 K.

Regards

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Cashflow filter data not coming all data

[chandra sekaran]

Hi
This is code wrriten by Rajan verma, it is small issues is there when
Filter  Collection journal in data sheet   it will not pickup all
collection journal data  in my out put sheet  what is issues below code



Dim rngRowData  As Range
Dim rngCell As Range
Dim wksOutPut   As Worksheet
Set wksOutPut = ThisWorkbook.Worksheets("Output")

Set rngRowData = Range("rngRowData").CurrentRegion
rngRowData.AutoFilter Field:=2, Criteria1:="Collection Journal"
Set rngRowData = rngRowData.SpecialCells(xlCellTypeVisible)
Set rngRowData = Intersect(rngRowData, rngRowData.Offset(1))
With Range("rngCollStart")

.Resize(Range("rngCollStart").CurrentRegion.Rows.Count,
6).ClearContents
.Resize(rngRowData.Rows.Count - 1, 1).Value =
rngRowData.Columns(3).Value
.Offset(, 1).Resize(rngRowData.Rows.Count - 1, 1).Value =
rngRowData.Columns(1).Value
.Offset(, 2).Resize(rngRowData.Rows.Count - 1, 1).Value =
rngRowData.Columns(7).Value
.Offset(, 3).Resize(rngRowData.Rows.Count - 1, 1).Value =
rngRowData.Columns(5).Value
.Offset(, 4).Resize(rngRowData.Rows.Count - 1, 1).Value =
rngRowData.Columns(6).Value
.Offset(, 5).Resize(rngRowData.Rows.Count - 1, 1).Value =
rngRowData.Columns(8).Value
End With
rngRowData.AutoFilter

Regards
chandru

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


CASHFLOW.xlsm
Description: Binary data

$$Excel-Macros$$ no mail since long

[Nemi Gandhi]

Since Jan 19, i am not getting mail from this group. Whats wrong? it is so
useful and learning.

Secondly, i am facing problem in getting result from cell link , which is
entered from another cell link. if in same cell, value is entered directly,
then formula works. why? what is solution.

-- 
Nemi Gandhi
98204 92963

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Text Box - Properties

[Cab Boose]

Hi Asa

Thankyou very much for the code.  Magic.  Will be using the info you
provided. Will include in ;my library.

Another similiar question being posted shortly re vlookup in text box,
another little project.

Thanks

Charlie

On Sun, Feb 5, 2012 at 2:37 PM, Asa Rossoff  wrote:

> The Active X Text Box control can be linked to a cell, but I think it is
> only available for use on a worksheet, not a userform… and everyone keeps
> telling me to avoid Active X controls on worksheets like the plague, anyway…
> 
>
> ** **
>
> To duplicate the linking behavior, you'll have to use a little VBA code.
> For example, in your form code you could use:
>
> Public TextBox1Cell As Range
>
> ** **
>
> Private Sub UserForm_Initialize()
>
> Set TextBox1Cell = ActiveWorkbook.Sheets("Sheet1").Range("A1")
>
> End Sub
>
> ** **
>
> Private Sub UserForm_Terminate()
>
> Set TextBox1Cell = Nothing
>
> End Sub
>
> ** **
>
> Private Sub UserForm_Activate()
>
> TextBox1.Value = TextBox1Cell.Value2
>
> End Sub
>
> ** **
>
> Private Sub TextBox1_Change()
>
> TextBox1Cell.Value2 = TextBox1.Value
>
> End Sub
>
> If you also need the control on the form to update if the worksheet cell
> is changed for some other reason while the form is still open (a
> calculation, other code,…) then you can add code to your worksheet's
> Calculate and Change events.  If the cell will only contain a value and not
> a formula, you don't need the calculate event.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> With UserForm1
>
> If .Visible Then
>
> If Not Application.Intersect(Target, .TextBox1Cell) Is Nothing
> Then
>
> .TextBox1.Value = .TextBox1Cell.Value2
>
> End If
>
> End If
>
> End With
>
> End Sub
>
> ** **
>
> Private Sub Worksheet_Calculate()
>
> With UserForm1
>
> If .Visible Then
>
> If .TextBox1.Value <> .TextBox1Cell.Value2 Then
>
> .TextBox1.Value = .TextBox1Cell.Value2
>
> End If
>
> End If
>
> End With
>
> End Sub
>
> ** **
>
> Asa
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Cab Boose
> *Sent:* Saturday, February 04, 2012 4:27 PM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Text Box - Properties
>
> ** **
>
> Hi
>
>  
>
> I have a userform open with a 'text box' .  Want to link with a cell in
> sheet.
>
>  
>
> However the 'text box'  properties does not show a 'linked cell'
> heading. I am sure it use to.  How do I restore the missing property or
> is there another text box I should use.
>
>  
>
> Thks
>
>  
>
> Charlie
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions rega

Re: $$Excel-Macros$$ no mail since long

[xlstime]

Hi Nemi,


Check your group membership setting  ( go to edit my membership (*
https://groups.google.com/group/excel-macros/subscribe?hl=en*
))...

and your second question i think it is sheet calculation problem please
send the example





On Sun, Feb 5, 2012 at 11:59 AM, Nemi Gandhi  wrote:

> Since Jan 19, i am not getting mail from this group. Whats wrong? it is so
> useful and learning.
>
> Secondly, i am facing problem in getting result from cell link , which is
> entered from another cell link. if in same cell, value is entered directly,
> then formula works. why? what is solution.
>
> --
> Nemi Gandhi
> 98204 92963
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
.

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ To excel macro team

[xlstime]

Hi Pascal,


Check your group membership setting  ( go to edit my membership (*
https://groups.google.com/group/excel-macros/subscribe?hl=en*
))...


On Thu, Feb 2, 2012 at 3:18 AM, Pascal Baro  wrote:

> Hi,
> I'm not receiving mail summary from group posting anymore. Is there any
> reasons?
> Regards,
> Pascal
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
.

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Most Helpful Member Jan'12 - Noorain Ansari

[Rohan Young]

CONGRATULATION NOORAIN BHAI, CARRY ON.

On Sat, Feb 4, 2012 at 10:47 AM, Venkat CV  wrote:

> Congrats...
>
> Noorain & Everyone...
>
> *Best Regards,*
> *Venkat *
> *Chennai*
> *My Linked in profile 
> *
>
>
> On Wed, Feb 1, 2012 at 10:11 PM, Ayush Jain  wrote:
>
>>  Hello Everyone,
>>
>> Noorain Ansari has been selected as 'Most Helpful Member' for the month
>> of Jan'12
>> He has posted 175 posts in Jan'12 and helped many people through his
>> expertise. He has been consistent contributor to this excel forum but has
>> acheived this recognition for first time.
>>
>> *About Noorain Ansari :*
>> [image: noorain.jpg]
>>
>> Noorain is a great poet and keep sharing his thoughts on different topics
>> throgh cool poems. Professionally, he is a Excel aficionado and presently
>> working as senior business analyst in One97 Communication Ltd. He is a MS
>> excel - vba expert and keep sharing his thoughts with this forum. You can
>> follow his blogs for Excel knowledge and cool poems :
>> www.excel-macro.blogspot.com & www.noorain-ansari.blogspot.com
>>
>> *Noorain, Many thanks for all your great support to group. Keep it up*
>>
>> *Thanks to Rajan Verma, Sam, Don Guillett, Kris, Bheema, Venkat, Chandru
>> , Priyanka, Lokesh and other folks for helping excel enthusiasts
>> voluntarily !! Keep it up !!
>> **
>> *Keep posting.
>>
>> Regards
>> Ayush Jain
>> Group Manager
>> Microsoft MVP
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> *
> *
> *
> *
>
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com
<>

RE: $$Excel-Macros$$ vlookup in Text Box

[Asa Rossoff]

Hi Charlie,

Glad my last post helped :)

 

>From a quick look I see two issues/potential issues:

1.   The syntax error:

TextBox5.Value = WorksheetFunction.VLookup(TextBox8.Value,
Range.("DBase"),2,False)

Remove the "." between "Range" and ("Dbase"):

TextBox5.Value = WorksheetFunction.VLookup(TextBox8.Value, Range
("DBase"),2,False)

2.   The Defined Names (ranges) in your workbook.  They include the
header row, which can interfere with lookup functions.  Not a high
likelihood in the exact usage that you have here, but still might be a good
idea to change.

 

P.S. In my last post, I used ActiveWorkbook.Worksheets(.).Range(.) in the
Userform_Initialize event to set up the linked cell.  You can consider if
ThisWorkbook is more appropriate than ActiveWorkbook in a given instance.  I
actually recommend always using ThisWorkbook if the workbook containing the
VBA code/userform is the workbook you mean and only using ActiveWorkbook
when you might mean another workbook---that which is on-screen at any given
moment.  Avoid pitfalls that way.  Whoops :)

Asa

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Cab Boose
Sent: Saturday, February 04, 2012 10:48 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ vlookup in Text Box

 

Hi

 

See attached worksheet.   Excel2000

 

I have  userform1  and one of the codes is to lookup a part # (in Textbox
8)( say Part # 3 is to be entered here) in a named range called  DBase
Once located to place the description in  textbox 5(named Description)

 

This is the code as per attached workbook,  have played around with the code
but still not working.  Currently get a message for Syntax error.

 

 

 

 

Private Sub TextBox8_Change()
TextBox5.Value = WorksheetFunction.VLookup(TextBox8.Value,
Range.("DBase"),2,False)
End Sub

 

So if 3000 entered in Part # text box8  I would expect to get in Textbox 5
the description:Print.Download-1/64...

 

Will have a 1000 + rows eventually. Will make the named range dynamic as
well.

 

Not much code to check.  Your advice much appreciated.

Thanks

 

Charlie

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com